VBAでAccessデータベースを活用しよう～テーブルの新規作成、削除編～

f:id:takehito33711:20181219194316p:plain

前回の記事ではAccessのテーブル名、更新日時、列名、データ型を取得しました。

今回はAccessのテーブルの新規作成と削除をVBAを使って行っていきます。

テーブルを新規作成しよう

テーブルの新規作成は下のコードで実行可能です。

下のコードではテーブル新規作成のときに列(カラム)名とデータ型を指定しています。

データ型は以下のサイトが参考になります。

www.accessclub.jp

テーブル新規作成のコード

　Public Sub 新規テーブル作成()
'参照設定：Microsoft ActiveX Data Objects 6.1 Library
'参照設定：Microsoft ADO Ext.6.0 for DDL and Security

　Dim TB As ADOX.Table
　Dim CAT As ADOX.Catalog
　Dim DBFile As String
　Dim strDB As String
    
    DBFile = "絶対パス(ここにはデータベースファイルがある場所を入力).accdb"
    strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile

    Set CAT = New ADOX.Catalog
    CAT.ActiveConnection = strDB
    
    Set TB = New ADOX.Table
    TB.Name = "新しいてーぶる"
    With TB
          .Columns.Append "テキスト", adVarWChar, 50
          .Columns.Append "空白込テキスト", adWChar, 10
          .Columns.Append  "日付", adDate
          .Columns.Append  "数字", adInteger
    End with
    CAT.Tables.Append TB
    
    Set TB = Nothing
    Set CAT = Nothing
   
　MsgBox "テーブルを作成しました"

簡単に解説していくよ

5~6行目

テーブルの新規作成にはADOXのTableオブジェクトとCatalogオブジェクトを使うことでできるようになります。
そこで今回5~6行目にTableオブジェクトとCatalogオブジェクトをこれから使いますよ～と宣言します。

7~11行目

7~11行目はファイルの場所を示す部分です。
これがないとVBAさんは、どこの何ていう名前のデータベースにテーブルを新規作成すればいいのかわからなくなります。

13~14行目

13~14行目はCatalogオブジェクトを使う儀式のようなものです。
始めに宣言した後、ここでCatalogオブジェクトを召喚するわけです。

16~24行目

16~24行目はTableオブジェクトを使ってTableの作成と列(カラム)名、データ型を指定しています。
17行目は新規作成するテーブルの名前を入れてます。

18行目以降はデータを入れる列の名前とその型を指定しています。
adVarWCharとadWCharはテキスト型、adDateは日付型、adIntegerは数値型です。
テキスト型にはデータサイズを数字で入れることができます。
この数字が大きいほど多くの文字数を入れれます。

数値型に指定した列にはテキストを入れることができません。
例えばadVarWcharでテキスト型にした列には、"晴れ"というデータを入れることができますが、
adIntergerで数値型にした列に"晴れ"というデータを入れることはできません。

データ型の指定には、自分がどんなデータを入れたいのか意識して設定しましょう。

ところで何でテキスト型にはadVarWCharとadWCharの2種類があるのかと思われたのではないでしょうか。
この2つは可変長と固定長の違いがあります。

例えば列名を「食べ物」にして「サンドイッチ」というデータを入れたいとします。
(.Columns.Append "食べ物", adWChar, 10)

このコードによってadWCar10分の長さが確保できましたが、固定長なので
サンドイッチ〇〇〇〇　(〇は空白)
というデータになり、「おにぎり」というデータを入れたいとすると、
おにぎり〇〇〇〇〇〇
という風に固定長の長さになるまで空白が入れられます。

可変長(adVarWChar)では空白は自動で入れられず、入力した長さがデータとして保存されます。

26~27行目

26~27行目は召喚したTableオブジェクトとCatalogオブジェクトを解放しています。
この解放を行ってあげないと彼らはPCのどこかに潜んでいることになります。

テーブルの削除をしよう

続いてテーブルの削除をしていきましょう。

Public Sub 新規テーブル作成()
'参照設定：Microsoft ActiveX Data Objects 6.1 Library
'参照設定：Microsoft ADO Ext.6.0 for DDL and Security

Dim CAT As ADOX.Catalog
Dim TB As ADOX.Table
Dim strDB As String
Dim DBFile As String
     
DBFile = "絶対パス(ここにはデータベースファイルがある場所を入力).accdb"
strDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
     
Set CAT = New ADOX.Catalog
CAT.ActiveConnection = strDB
CAT.Tables.Delete "テーブル"
     
Set CAT = Nothing
Set TB = Nothing
     
MsgBox "テーブルを削除しました"
     
End Sub