Accessファイルを作る(ExcelでRDBその2)

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

hirocom777.hatenadiary.org

Accessファイルを作ってみる

　何はともあれAccessファイルを作ることです。ファイルを作れないことには話になりません。Excelファイルをデータベース操作するときはADODB.Connectionオブジェクトを使用していましたが、こちらはファイル作成が出来ません。そもそも、Excelで操作しているわけですからファイル作成自体はExcel自身で十分なわけです。ではどうするかというと、Microsoft ADO Ext.6.0 for DDL and Securityを使用します。まず、任意のExcelファイルを作成したら、VBEのメニューでツール⇒参照設定で参照設定画面を開いてMicrosoft ADO Ext.6.0 for DDL and Securityにチェックを入れてください。次に、標準モジュールを作成して以下のプロシージャを入力してみてください。

'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

え？たったのこれだけ・・・で、このプロシージャを実行してみると・・・用意したExcelファイルと同じ場所にAccessのデータベースファイル"AccessDB.accdb"か゜出来上がります。たったのこれだけなんですね。では、早速ファイルの中身を覗いてみましょう。と、言ってもAccessは持っていませんのでExcelで開いてみます。Excelを開いたら作った"AccessDB.accdb"をドラック＆ドロップしてみます。と、僕の環境では『セキュリティーに関する通知』というアナウンスが出てきます。有効にするか無効にするかを聞いてきますので、有効にするを選択すると・・・

そりゃそうですよね。ファイル作っただけですから。データはおろか、テーブルだってないんですもの。それではテーブルを追加する方法を用意しましょう。一緒にフィールドも作ります。上記の標準モジュールに以下のプロシージャを追加して実行してみてください。

'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

実行すると"AccessDB.accdb"のファイルサイズがちょっぴり増えましたよね。Excelにファイルをドラック＆ドロップしてみると、今度は『セキュリティーに関する通知』の後が以下の様になりました。

お、なんかよさそうじゃないですか。OKを押してみると・・・

あー、テーブルが出来ています！！ここまでくればしめたもの。データの追加や削除、編集をADODB.Connectionオブジェクトを使って実行していけばいいんです。ちなみに、AddTable内の　tableObject.Name = "Table_1"　を　tableObject.Name = "Table_2"　に変えて再度実行するとファイルサイズがもう少し増えます。同じように開いてみるとセキュリティーに関する通知の後に以下の画面が出てきます。