朝会発表テーマ

Accessテーブル定義自動出力

2019年06月24日

ExcelVBAマクロ応用
サンプルコード※超省略版

'   [参照設定]
'   ・Microsoft Scripting Runtime
'   ・Windows Script Host Object Model
'   ・Microsoft DAO 3.6 Object Library
    Set objMdb = DBEngine.OpenDatabase(strFileName) ' ---------- MDBに接続
    For Each objTbl In objMdb.TableDefs ' ---------- テーブルを順次取得
        テーブル名 = objTbl.Name
        テーブル説明 = objTbl.Properties(g_cnsDescription) 
            For Each objFld In objTbl.Fields ' ---------- テーブル内のフィールドを順次取得
                フィールド説明 = objFld.Properties(g_cnsDescription)
                フィールド名 = objFld.Name
                フィールド型 = objFld.Type
                フィールドサイズ = objFld.Size
            Next objFld            
            For Each objIdx In objTbl.Indexes ' ---------- インデックスを展開
                プライマリキー = objIdx.Primary
                For Each objFld In objIdx.Fields ' ---------- インデックス内のフィールドを展開
                    インデックス名 = objFld.Name
                Next objFld
            Next objIdx
    Next objTbl
    objMdb.Close

テーブル定義書例：


目次例：

参考リンク
VBA応用(MDBのテーブル定義内容を取得する。)｜☆ Excelでお仕事！(Excel全般の解説サイト) ☆　←使いやすいようにカスタマイズしてご利用ください！
 

Accessテーブル定義自動出力
本来はデータベース設計とテーブル定義書が先にあって、そのテーブル定義書に従ってテーブルを作成するものです。でも実際の運用では、開発が先でドキュメントが後になることもあれば、テーブル定義の変更が発生して、いちいち定義書を書き換えなければならないこともよくあります。ドキュメントの微修正が面倒だったので、Accessからテーブル定義を自動出力する方法がないか調べました。
Accessシステムから自動的にテーブル定義書を作成する方法
既存のAccessの機能として、データベースツールの「データベース構造の解析」から、簡易なテーブル定義情報を出力することは可能です。ただ、テーブルレイアウトでは無いので、ページ数が多くなり、あまり見やすいドキュメントではありません。
Excel VBAなどを応用して、一般的なテーブル定義書のフォーマットでドキュメントを自動作成してくれるサンプルコードはないかなと思って検索したら、ありました。
作成済みのAccessテーブル定義内容をワークシート上に書き出します。
コードの細かい処理の部分を省略して、大まかな流れだけがわかるように書き直したものを載せています。
まずファイルパスを指定してデータベースに接続、テーブル定義をループして、テーブル情報を一つずつ取出し、書き出していきます。
テーブルに含まれるフィールド情報およびインデックス情報を取り出すループがが入れ子になっています。
(ADOXではMDBの全機能が設定できないことから、 DAOを利用しています。)
データベース接続するためのライブラリなど、追加する必要がある参照設定を明記してあります。もし動かなかったら、参照設定が足りているかどうか確認してください。
Jドライブにファイルサンプルを置いてありますので、今後Accessデータベースで定義書を作ったり、定義からテーブルを作成したりする場合にご利用ください。既存のテーブルからテーブル定義書を自動出力することができる＋テーブル定義書からAccessテーブルを自動作成する、両方の機能が入ったマクロです。また、ネットからダウンロードしたファイルを少し改変して、テーブル名一覧から各テーブル名のシートに飛ぶ、目次ページを出力するような処理を追加したものです。今まではツールを社内で共有していなかったので、Accessでテーブルを作るのも、定義書を書くのも、いちいち手で作成する必要があったと思います。今後は、手作業で時間のかかっていた作業を効率化できると良いなと思います。

コメント