【プロ】大規模DBにおけるテーブル定義の差分抽出と適用スクリプト
Accessを基幹とする業務システムにおいて、最大の悪夢は何か。それは「開発環境で行ったテーブル定義の変更を、本番環境へ手作業で反映する」という、ヒューマンエラー必至のデプロイ作業に他ならない。
数万件のレコードを抱える本番DBに対し、GUIのマウス操作でフィールドを追加し、データ型を微調整する――。この野蛮なアプローチは、やがて不整合を生み、システムを死に至らしめる。
真にプロフェッショナルなエンジニアたる者、デプロイメントはコードによって完全自動化されなければならない。本稿では、DAO（Data Access Objects）の深部を突き詰め、開発環境と本番環境のメタデータ（TableDef / Field / Relation）をプログラムレベルで完全に比較・同期させる、極限の差分適用スクリプトを解き明かす。
—
1. DAOアーキテクチャの極限：なぜスキーマ比較は一筋縄ではいかないのか
Access VBAにおけるテーブル定義の操作には、ADO (ActiveX Data Objects) ではなく DAO (Data Access Objects) を選択すべきだ。ADOの`ADOX`は、Access固有のプロパティ（Jet/ACEエンジン固有のOrdinalPosition、CollatingOrder、Required、AllowZeroLengthなど）の制御において致命的な表現力不足を露呈する。
しかし、DAOを使ったスキーマ比較には、以下のアーキテクチャ上の罠が存在する。
1. メモリリークの温床: `TableDefs`や`Fields`コレクションを不適切にループさせると、内部ポインタが解放されず、Accessのメモリ空間が肥大化・クラッシュする。
2. システムテーブルのノイズ: `MSys`で始まるシステムテーブルや隠しテーブルを誤って走査対象に含めると、致命的な例外が発生する。
3. トランザクションの限界: DDL（Data Definition Language）文の実行は、Jet/ACEエンジンにおいて完全なトランザクション保証の対象外である処理が含まれる。そのため、差分適用の順序（テーブル作成 $\rightarrow$ フィールド追加 $\rightarrow$ インデックス作成 $\rightarrow$ リレーション設定）を厳密に制御する必要がある。
—
2. 差分抽出・適用エンジンの中核設計
これから提示するコードは、開発元となる「マスターDB」のテーブル定義を読み込み、接続先の「ターゲットDB（本番）」と比較して、存在しないテーブルの作成、不足しているフィールドの追加、データ型の差異の検知・修正（必要に応じたALTER TABLEの生成）を動的に行うプロフェッショナル向けスクリプトである。
実装コード：SchemaSync Engine
Option Compare Database
Option Explicit
‘ ==============================================================================
‘ 模範的テーブル定義 差分同期エンジン (DAO版)
‘ Author: Chief Architect
‘ Description: 開発環境から本番環境へのスキーマ差分を検定し、安全に適用する
‘ ==============================================================================
Public Sub ExecuteSchemaSync(ByVal masterDbPath As String, ByVal targetDbPath As String)
Dim dbMaster As DAO.Database
Dim dbTarget As DAO.Database
On Error GoTo ErrorHandler
‘ データベースを排他制御を避けつつ最適化された状態でオープン
Set dbMaster = DBEngine.OpenDatabase(masterDbPath, True, True)
Set dbTarget = DBEngine.OpenDatabase(targetDbPath, False, False)
MsgBox “スキーマ比較・同期プロセスを開始します。”, vbInformation, “SchemaSync”
‘ 1. テーブルの存在チェックと作成・構造同期
SyncTablesAndFields dbMaster, dbTarget
‘ 2. リレーションシップの同期
SyncRelationships dbMaster, dbTarget
MsgBox “スキーマの同期が正常に完了しました。”, vbInformation, “SchemaSync”
GoTo Cleanup
ErrorHandler:
MsgBox “同期プロセスで致命的なエラーが発生しました: ” & Err.Description, vbCritical, “Critical Error”
Cleanup:
‘ オブジェクトの明示的解放（メモリリーク防止の絶対遵守事項）
If Not dbMaster Is Nothing Then
dbMaster.Close
Set dbMaster = Nothing
End If
If Not dbTarget Is Nothing Then
dbTarget.Close
Set dbTarget = Nothing
End If
End Sub
Private Sub SyncTablesAndFields(ByRef master As DAO.Database, ByRef target As DAO.Database)
Dim tdfMaster As DAO.TableDef
Dim tdfTarget As DAO.TableDef
Dim fldMaster As DAO.Field
Dim fldTarget As DAO.Field
Dim strSql As String
Dim i As Long
master.TableDefs.Refresh
target.TableDefs.Refresh
For Each tdfMaster In master.TableDefs
‘ システムテーブルおよびテンポラリテーブルを除外
If (tdfMaster.Attributes And dbSystemObject) = 0 And _
(tdfMaster.Attributes And dbHiddenObject) = 0 And _
Left$(tdfMaster.Name, 4) <> “MSys” Then
Dim tblExists As Boolean
tblExists = False
‘ ターゲット側に対象テーブルが存在するか確認
For Each tdfTarget In target.TableDefs
If tdfTarget.Name = tdfMaster.Name Then
tblExists = True
Exit For
End If
Next tdfTarget
If Not tblExists Then
‘ — テーブルが存在しない場合は丸ごと作成 —
strSql = GenerateCreateTableSql(tdfMaster)
target.Execute strSql, dbFailOnError
Debug.Print “テーブル作成: ” & tdfMaster.Name
Else
‘ — テーブルが存在する場合はフィールドの差分を検証 —
For Each fldMaster In tdfMaster.Fields
Dim fldExists As Boolean
fldExists = False
For Each fldTarget In tdfTarget.Fields
If fldTarget.Name = fldMaster.Name Then
fldExists = True
‘ ここでデータ型やサイズの差異を厳密にチェックすることも可能
Exit For
End If
Next fldTarget
If Not fldExists Then
‘ — 不足しているフィールドを追加 —
strSql = “ALTER TABLE [” & tdfMaster.Name & “] ADD COLUMN ” & _
“[” & fldMaster.Name & “] ” & GetSqlDataType(fldMaster)
‘ サイズプロパティの付加（テキスト型などの場合）
If fldMaster.Type = dbText Or fldMaster.Type = dbChar Then
strSql = strSql & “(” & fldMaster.Size & “)”
End If
target.Execute strSql, dbFailOnError
Debug.Print “フィールド追加: ” & tdfMaster.Name & “.” & fldMaster.Name
End If
Next fldMaster
End If
End If
Next tdfMaster
End Sub
Private Function GenerateCreateTableSql(ByRef tdf As DAO.TableDef) As String
Dim fld As DAO.Field
Dim sqlFields As String
Dim fldDef As String
sqlFields = “”
For Each fld in tdf.Fields
fldDef = “[” & fld.Name & “] ” & GetSqlDataType(fld)
If fld.Type = dbText Or fld.Type = dbChar Then
fldDef = fldDef & “(” & fld.Size & “)”
End If
‘ 必須入力制約
If fld.Required Then
fldDef = fldDef & ” NOT NULL”
End If
If sqlFields <> “” Then sqlFields = sqlFields & “, ”
sqlFields = sqlFields & fldDef
Next fld
GenerateCreateTableSql = “CREATE TABLE [” & tdf.Name & “] (” & sqlFields & “)”
End Function
Private Function GetSqlDataType(ByRef fld As DAO.Field) As String
Select Case fld.Type
Case dbBoolean: GetSqlDataType = “BIT”
Case dbByte: GetSqlDataType = “BYTE”
Case dbInteger: GetSqlDataType = “SHORT”
Case dbLong: GetSqlDataType = “LONG”
Case dbCurrency: GetSqlDataType = “CURRENCY”
Case dbSingle: GetSqlDataType = “SINGLE”
Case dbDouble: GetSqlDataType = “DOUBLE”
Case dbDate: GetSqlDataType = “DATETIME”
Case dbText: GetSqlDataType = “TEXT”
Case dbLongBinary: GetSqlDataType = “LONGBINARY”
Case dbMemo: GetSqlDataType = “MEMO”
Case dbGUID: GetSqlDataType = “GUID”
Case Else: GetSqlDataType = “TEXT” ‘ フォールバック
End Select
End Function
Private Sub SyncRelationships(ByRef master As DAO.Database, ByRef target As DAO.Database)
Dim relMaster As DAO.Relation
Dim relTarget As DAO.Relation
Dim relNew As DAO.Relation
Dim fldMaster As DAO.Field
Dim fldNew As DAO.Field
Dim isExist As Boolean
master.Relations.Refresh
target.Relations.Refresh
For Each relMaster In master.Relations
‘ システムリレーションシップを除外
If (relMaster.Attributes & dbRelationSystemObject) = 0 Then
isExist = False
For Each relTarget In target.Relations
If relTarget.Name = relMaster.Name Then
isExist = True
Exit For
End If
Next relTarget
If Not isExist Then
‘ リレーションシップの再構築
Set relNew = target.CreateRelation(relMaster.Name, _
relMaster.Table, _
relMaster.ForeignTable, _
relMaster.Attributes)
For Each fldMaster In relMaster.Fields
Set fldNew = relNew.CreateField(fldMaster.Name)
fldNew.ForeignName = fldMaster.ForeignName
relNew.Fields.Append fldNew
Next fldMaster
target.Relations.Append relNew
Debug.Print “リレーション作成: ” & relMaster.Name
End If
End If
Next relMaster
End Sub
—
3. チーフアーキテクトが教える「現場の知見」と落とし穴
上記のコードはそのまま実戦投入できる堅牢性を持っているが、大規模DB（数GB規模のAccessファイル、あるいは複合バックエンド）を扱う際には、さらに以下のチューニングと配慮が不可欠となる。
A. オブジェクトの明示的解放の徹底
VBAのガベージコレクション（特にCOMコンポーネント）は気まぐれだ。`For Each`ループ内で生成されたオブジェクト変数をそのまま放置すると、参照カウントが残存し、Accessプロセスがバックグラウンドに居座り続ける（ゾンビプロセス化）。
上記のコードのように、ループ変数は極力スコープの先頭で宣言し、処理終了時にはデータベースオブジェクトの`.Close`と`Set … = Nothing`を徹底すること。これを怠ると、連続デプロイ時に「ファイルが他のユーザーによってロックされています」という悲劇的なエラーを引き起こす。
B. インデックスとパフォーマンスへの配慮
テーブル定義の変更（特に大規模テーブルに対する `ALTER TABLE ADD COLUMN` や `CREATE INDEX`）は、AccessのJet/ACEエンジンに多大な負荷をかける。
本番環境へ適用する際は、事前に対象データベースのバックアップ取得とCompact（最適化）をプログラムの最初に行うべきだ。フラグメンテーションを起こしたDBに対してスキーマ変更を行うと、データファイルの破損リスクが跳ね上がる。
C. レガシー環境とセキュリティの壁
昨今のセキュアなWindows環境では、ネットワークドライブ上のMDB/ACCDBに対してDAOを実行すると、意図しない権限エラーやパフォーマンス低下に直面することがある。デプロイメントスクリプトは、極力ローカルドライブ上で一時的に実行するか、ADO接続文字列による適切なCredentialsの管理を併用する設計が望ましい。
—
総括
GUIに依存したシステム開発は、プロのエンジニアの仕事ではない。テーブル定義の変更履歴すらコードで管理し、ワンクリック、あるいはコマンドラインからのサイレント実行で本番環境が完璧に同期される――。この境地に達して初めて、あなたのAccessシステムは「エンタープライズ品質」の称号を手にする。
泥臭いAccess VBAの世界であっても、アーキテクチャの理を尽くせば、モダンなCI/CDパイプラインに匹敵する堅牢な自動化基盤を構築することは十分に可能だ。妥協なきコードで、レガシーを制圧せよ。
