前回記事にてExcelVBAマクロを使ってaccdb(mdb)への接続(とデータの取得)を行いました。
今回はテーブルの作成と削除、フィールドの追加と削除について紹介します。
これができるようになればAccess無しでDB連携のExcelVBAマクロを作ることができるようになります。
AccessのDBを準備
accdb(mdb)を扱う記事ではおなじみになりました、DBです。
以下からダウンロードできます。(zip圧縮してあります)
なお、中身のデータはVBA100本ノックの「VBA100本ノック 9本目：フィルターコピー」のデータになっています。
ExcelVBAマクロを準備
続いてダウンロードしたaccdb(mdb)を制御するためのExcelVBAマクロを準備します。
ソースコードは以下からダウンロードできます。(内容の説明は実際に動作させてから行います)
テキストファイルになっているのでその内容すべてをコピーし、任意のExcelファイルに標準モジュールを追加して貼り付けてください。
また、正常に動作させるためには以下の参照設定を行う必要があります。
・ADODB
・ADOX
バージョンについては環境により変わると思いますので最新のバージョンを使ってみてください。
それと、Excelファイルと同じフォルダへaccdb(mdb)を配置しておいてください。
全ての準備が整ったら実際にExcelVBAマクロを実行してみましょう。
テーブル情報の取得
Accessが無い状況での作業を想定しているので何はなくともデータベースの中身を把握できる物が必要になってきます。
そこでまずはデータベースの中身を確認できるテーブル情報の取得用関数「showTableInfo()」を実行し、中身を確認してみましょう。
テーブル名「tblScore」に7つのフィールドが存在している事が確認できました。
プログラムを見ながら中身の処理について解説していきます。
「情報収集」「データベースへ接続」「データベースを閉じる」「Connectionを開放(破棄)」の部分は前回記事にて説明済みなので省略します。
「テーブル情報の取得」ではデータベースへの接続(ADODB.Connection)を使ってカタログ(ADOX.Catalog)とし、テーブル(ADOX.Table)とフィールド(ADOX.Column)を取り出して、それぞれをイミディエイトウィンドウへ出力するようにしています。
データ構造としては以下のようになっています。
Catalog
┗Tables
┗Table
┗Columns
┗Column
この関数にてデバッグエラーが発生する場合、「データベースが見つからない」「参照設定ができていない」あたりが可能性として大きいのでそこらへんを確認してみてください。
テーブルの作成
データベースの中身の確認ができたので早速新規でテーブルを作成してみましょう。
テーブルの作成用関数「createTable()」を実行します。
実行後はテーブル情報の取得用関数「showTableInfo()」を実行し、中身を確認してみましょう。
テーブル「tblNew」が追加されている事が確認できました。
プログラムを見ながら中身の処理について解説していきます。
「情報収集」「データベースへ接続」「データベースを閉じる」「Connectionを開放(破棄)」の部分は前回記事にて説明済みなので省略します。
「テーブルの作成」「カタログに追加」では新規で作成したテーブルをカタログに追加(Append)しています。
この関数にてデバッグエラーが発生する場合、「同名のテーブルが既に存在している」可能性が高いのでテーブル情報の取得用関数「showTableInfo()」を実行し、中身を確認してみましょう。
フィールドの追加
テーブルを作成しましたが、フィールドがまだ無いのでフィールドを追加しましょう。
フィールドの追加用関数「addColumn()」を実行します。
実行後はテーブル情報の取得用関数「showTableInfo()」を実行し、中身を確認してみましょう。
テーブル「tblNew」に3つのフィールドが追加されている事が確認できました。
プログラムを見ながら中身の処理について解説していきます。
「情報収集」「データベースへ接続」「データベースを閉じる」「Connectionを開放(破棄)」の部分は前回記事にて説明済みなので省略します。
「フィールドの追加」では作成したテーブルにフィールド名と型を指定して追加(Append)しています。
型についてはMicrosoftのページにて詳しく記載があるので参考にしてください。
フィールドの削除
続いてフィールドを削除してみましょう。
フィールドの削除用関数「deleteColumn()」を実行します。
実行後はテーブル情報の取得用関数「showTableInfo()」を実行し、中身を確認してみましょう。
テーブル「tblNew」からフィールド「sMemo」が削除されている事が確認できました。
プログラムを見ながら中身の処理について解説していきます。
「情報収集」「データベースへ接続」「データベースを閉じる」「Connectionを開放(破棄)」の部分は前回記事にて説明済みなので省略します。
「フィールドの削除」では追加したフィールドを名前で指定して削除(Delete)しています。
テーブルの削除
最後にテーブルを削除してみましょう。
テーブルの削除用関数「deleteTable()」を実行します。
実行後はテーブル情報の取得用関数「showTableInfo()」を実行し、中身を確認してみましょう。
テーブル「tblNew」が削除されている事が確認できました。
プログラムを見ながら中身の処理について解説していきます。
「情報収集」「データベースへ接続」「データベースを閉じる」「Connectionを開放(破棄)」の部分は前回記事にて説明済みなので省略します。
「テーブルの削除」では作成したテーブルを名前で指定して削除(Delete)しています。
まとめ
テーブルの作成・削除、フィールドの追加・削除について紹介しました。
データ構造を理解すれば難しいところはなく理解できると思います。
前回同様今回のプログラムはaccdb向けに作成していますが「FILE_NAME_DB」をmdb側に変更する事でmdb向けでデータベースの操作ができます。(全部で5か所変更が必要なのでご注意ください)
今回紹介したプログラムはかなり冗長な書き方をしていますが1つのプロシージャで完結するような紹介をしたいためにこのような書き方にしています。
次回はこの冗長になったプログラムを最適化していく方法を紹介して柔軟に対応できるプログラムを紹介しようと思います。
コメント