ADOXって何ですか？(ExcelでRDBその3)

ExcelでRDB（リレーショナルデータベース）を操作してみようという連載のその3です。前回の記事はこちらです。

hirocom777.hatenadiary.org

ADOXって何？

　Accessファイル、あっさり作れましたね。簡単でした。それにしてもMicrosoft ADO Ext.6.0 for DDL and Security（以後ADOX）。なんなんでしょう？ADOXは、簡単に言うとADOの拡張機能。ADOでは出来なかったことが出来るようになります。例えば、前回やったデータベースファイルの作成、テーブルやフィールドの作成、削除、設定（こちらはADOでもSQLで出来ると思います。）、テーブル一覧の取得なんかです。データベースファイルやテーブル、フィールドの設定はADOX、実際のデータ操作はADOみたいな使い分けをするんでしょうか？この辺はおいおい深堀りしていこうと思います。まずは前回のコードの解説です。

ファィル作成の解説

　以下に、前回作成したAccessデータベースファイルを作るコードを再掲します。

'Accessデータベースファイルを作ります
Public Sub MakeAccessFile()
Dim fileName As String
Dim connectString As String
Dim catalogObject As Object
Set catalogObject = CreateObject("ADOX.Catalog")
  'データベースファイル名
  fileName = ThisWorkbook.Path & "\AccessDB.accdb"
  'データベース接続文字列（Access2007以降）
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  'ファイル作成
  catalogObject.Create connectString
End Sub

　シンプルですね。ファイル名を指定してADOXのcatalogオブジェクトを作成。接続文字を指定してデータベースファイルを作成します。catalogというのは、データベースでデータの一覧とかを意味することのようで複数のテーブルを有するデータベースファイルを意味しています。データベース接続文字列でファイル形式とファイル名を指定します。"Provider="の後を"Microsoft.Jet.OLEDB.4.0"とすると、ファイル形式がAccess2000（*.mdb）になります。後はcatalogObject.Create connectStringでファイルを作るだけ！！

テーブル、フィールド作成の解説

　続いてテーブル、フィールド作成の解説です。以下に、前回のコードを再掲しますね。

'Accessデータベースファイルにテーブルを追加します
Public Sub AddTable()
Dim fileName As String
Dim connectString As String
Dim catalogObject As Object
Dim tableObject As Object
Set catalogObject = CreateObject("ADOX.Catalog")
Set tableObject = New ADOX.Table
  'データベースファイル名
  fileName = ThisWorkbook.Path & "\AccessDB.accdb"
  'データベース接続文字列（Access2007以降）
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  'ファイルに接続
  catalogObject.ActiveConnection = connectString
  'テーブル作成
  tableObject.Name = "Table_1"
  'フィールド作成
  tableObject.Columns.Append "登録ID", adInteger
  tableObject.Columns.Append "氏名", adVarWChar
  tableObject.Columns.Append "生年月日", adDate
  tableObject.Columns.Append "備考", adLongVarWChar
  'テーブル追加
  catalogObject.Tables.Append tableObject
End Sub

　データベースファイルの接続とかは、ファイル作成のときと同じです。お次はテーブルオブジェクトを作成してテーブル名を指定。。続いてテーブルにフィールドを追加設定していきます。指定する内容は、フィールド名とデータ型です。データ型指定の定数は沢山あるのですが、代表的なものは以下になります。

adInteger　　整数型
adDouble　　倍精度浮動小数点型
adDate　　　日付型
adVarWChar　文字列型（ Unicode ）
adLongVarWChar　長文字列型（ Unicode ）

最後にデータベースファイルにテーブルを追加して完了です。

ちょっと整理してみる

　ファイルを作成できましたが、２つのプロシージャに分かれています。かぶっている部分もあるので少し整理してみましょう。ファイル名、テーブル名、フィールド名、フィールドのデータ型を指定してAccessファイルを作成するプロシージャを作ります。以下の様になりました。新規に作成した標準モジュールに以下を入力してみてください。

'Accessデータベースファイルを作ります
Public Sub MakeAccessDataBase(fileName As String, tableName As String, fieldName() As String, fieldType() As Integer)
Dim connectString As String
Dim catalogObject As Object
Dim tableObject As Object
Dim i As Long
Set catalogObject = CreateObject("ADOX.Catalog")
Set tableObject = New ADOX.Table
  'データベース接続文字列（Access2007以降）
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  'ファイルが無い場合はファイル作成
  If Dir$(fileName) = "" Then
    catalogObject.Create connectString
  Else
    catalogObject.ActiveConnection = connectString
  End If
  'テーブル名設定
  tableObject.Name = tableName
  'フィールドをテーブルに設定
  For i = 0 To UBound(fieldName)
    tableObject.Columns.Append fieldName(i), fieldType(i)
  Next i
  'テーブル追加
  catalogObject.Tables.Append tableObject
End Sub

データベース名、テーブル名、フィールド名リスト（配列）、フィールド型名リスト（配列）を指定して呼び出すと、指定した内容のAccessファイルが出来上がります。既存のAccessファイルを指定した場合には、テーブルの追加のみを行います。以下が使用例です。

'Accessデータベースファイル作成サンプル
Sub AccessSample()
Dim fileName As String
Dim tableName As String
Dim fieldName(3) As String
Dim fieldType(3) As Integer
  'ファイル名
  fileName = ThisWorkbook.Path & "\AccessDB.accdb"
  'テーブル名
  tableName = "Table_1"
  'フィールド設定
  fieldName(0) = "登録ID"
  fieldName(1) = "氏名"
  fieldName(2) = "生年月日"
  fieldName(3) = "備考"
  fieldType(0) = adInteger
  fieldType(1) = adVarWChar
  fieldType(2) = adDate
  fieldType(3) = adLongVarWChar
  'Accessファイル作成
  MakeAccessDataBase fileName, tableName, fieldName(), fieldType()
End Sub

上記を標準モジュールに入力して実行してみてください。前回と同じようにAccessファイルが出来ているはずです。