【実務・中級編】【プロ】大規模DBにおけるテーブル定義の差分抽出と適用スクリプト – Access VBA解析バイブル

【プロが教える】大規模DBにおけるテーブル定義の差分抽出と適用自動化

開発環境で作ったテーブル定義を、本番環境へどうやって反映させているか？
「本番のテーブルを一度削除して作り直す」「手動でフィールドを追加・修正する」——もしあなたが現場でこんな泥臭いオペレーションをしているなら、今すぐその手を止めてほしい。

大規模データベースにおいて、テーブルの再作成はデータロストのリスクと巨大なトランザクション負荷を伴う禁忌であり、手動変更は「環境間のズレ（ヒューマンエラー）」を生む最大の温床だ。

今回は、Access VBAのDAO（Data Access Objects）を極限までドライに使い倒し、開発環境と本番環境のテーブル定義（フィールド・データ型・サイズ）の差分を自動検出し、最小限のDDL（ALTER TABLE）で安全に適用するデプロイメント自動化スクリプトを授与しよう。

—

1. なぜ「力技のデプロイ」は失敗するのか？

DAOを用いたテーブル操作において、初心者が陥りがちな罠がいくつかある。

• `TableDef.Fields.Append` の誤用: 既存テーブルに対して安易にフィールドを追加しようとして、すでに同名が存在する場合のエラーハンドリングを怠る。
• データ型の不一致による暗黙の型変換エラー: Access（Jet/ACEエンジン）は型に寛容に見えて、ALTER時の型変更には非常に厳しい。特に長整数型（Long）とオートナンバー型の衝突や、テキスト型のサイズ変更におけるトランザクション制御の欠如。
• リレーションシップ（外部キー）の無視: フィールドを変更する際、外部キー制約（Relation）が張られたままテーブル構造をいじろうとして「インデックスが競合しています」というエラーで爆死する。

プロのアーキテクトが目指すべきは、「既存データを一切破壊せず、構造の差異のみを外科手術のように正確に同期させる仕組み」だ。

—

2. アーキテクチャ設計：差分抽出と適用のアルゴリズム

今回のスクリプトは、以下のロジックで動作する。

1. メタデータの正引き: 開発環境（マスター）と本番環境の `TableDefs` / `Fields` コレクションを走査し、メモリ上のディクショナリ（あるいは比較用構造体）へマッピング。
2. 差分のマトリクス化:

• 追加 (Add): 本番に存在しないフィールドを発見した場合。
• 変更 (Alter): 型、サイズ、必須プロパティ（`Required`）、空文字列許可（`AllowZeroLength`）の差異を発見した場合。

3. 安全なDDL生成と実行: `CurrentDb.Execute` を用い、トランザクション安全性を確保した上でSQLを発行。

—

3. 【プロダクションコード】差分自動適用エンジン

以下のコードは、外部の「開発環境用AccDB」を指定し、現在のデータベース（本番環境と仮定）とのテーブル定義の差分を比較・適用する実用モジュールだ。

標準モジュールに貼り付けて実行してほしい。エラーハンドリングとオブジェクトのクリーンアップ（解放）を徹底したプロダクションクオリティである。

Option Compare Database
Option Explicit

‘ =========================================================================
‘ 模範的デプロイメント自動化エンジン：テーブル定義差分同期
‘ =========================================================================
Public Sub SynchronizeTableDefinitions(ByVal targetTableName As String, ByVal masterDbPath As String)
Dim dbMaster As DAO.Database
Dim dbCurrent As DAO.Database
Dim tdfMaster As DAO.TableDef
Dim tdfCurrent As DAO.TableDef

Dim fldMaster As DAO.Field
Dim fldCurrent As DAO.Field

Dim sqlDDL As String
Dim isTableExist As Boolean
Dim fld As DAO.Field

On Error GoTo ErrorHandler

‘ 1. マスター（開発環境）DBを外部参照としてオープン
Set dbMaster = OpenDatabase(masterDbPath, True) ‘ 読み取り専用
Set dbCurrent = CurrentDb()

‘ マスターに指定テーブルが存在するか確認
If Not TableExists(dbMaster, targetTableName) Then
MsgBox “マスターDBに指定テーブル [” & targetTableName & “] が存在しません。”, vbCritical, “致命的エラー”
GoTo Cleanup
End If

Set tdfMaster = dbMaster.TableDefs(targetTableName)

‘ 2. 本番側にテーブルが存在するか？
isTableExist = TableExists(dbCurrent, targetTableName)

If Not isTableExist Then
‘ — 【ケースA】テーブル自体が存在しない場合は丸ごと作成 —
MsgBox “テーブル [” & targetTableName & “] が本番環境に存在しません。新規作成します。”, vbInformation, “デプロイ情報”
CreateNewTableFromMaster tdfMaster, dbCurrent
GoTo Cleanup
End If

‘ — 【ケースB】既存テーブルのフィールド単位の差分検証と適用 —
Set tdfCurrent = dbCurrent.TableDefs(targetTableName)

dbCurrent.BeginTrans ‘ トランザクション開始（安全性確保）

For Each fldMaster In tdfMaster.Fields
If Not FieldExists(tdfCurrent, fldMaster.Name) Then
‘ 2-1. 【追加】フィールドが存在しない場合
sqlDDL = “ALTER TABLE [” & targetTableName & “] ADD COLUMN [” & fldMaster.Name & “] ” & GetDataTypeSQLString(fldMaster)

‘ 属性（Required等）の付加
If fldMaster.Required Then sqlDDL = sqlDDL & ” NOT NULL”

dbCurrent.Execute sqlDDL, dbFailOnError
Debug.Print “追加: ” & fldMaster.Name

Else
‘ 2-2. 【変更チェック】既存フィールドの型やサイズ等の比較
Set fldCurrent = tdfCurrent.Fields(fldMaster.Name)

If (fldMaster.Type <> fldCurrent.Type) Or (fldMaster.Size <> fldCurrent.Size) Then
‘ 注意: 大規模DBにおける型変更はデータロスを伴うため、SQL Server等への移行を見据えログ出力に留めるか、慎重にALTERを実行
Debug.Print “警告: フィールド [” & fldMaster.Name & “] の型またはサイズが異なります。マスター(” & fldMaster.Type & “) / 現在(” & fldCurrent.Type & “)”

‘ 必要に応じたALTER COLUMN構文の実行（※Accessの制約に注意）
‘ sqlDDL = “ALTER TABLE [” & targetTableName & “] ALTER COLUMN [” & fldMaster.Name & “] ” & GetDataTypeSQLString(fldMaster)
‘ dbCurrent.Execute sqlDDL, dbFailOnError
End If
End If
Next fldMaster

dbCurrent.CommitTrans
MsgBox “テーブル [” & targetTableName & “] の定義同期が正常に完了しました。”, vbInformation, “デプロイ成功”

Cleanup:
On Error Resume Next
dbMaster.Close
Set dbMaster = Nothing
Set dbCurrent = Nothing
Exit Sub

ErrorHandler:
dbCurrent.RollbackTrans
MsgBox “デプロイ中にエラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“詳細: ” & Err.Description, vbCritical, “予期せぬエラー”
Resume Cleanup
End Sub

‘ — ヘルパー関数群 —

Private Function TableExists(db As DAO.Database, tableName As String) As Boolean
Dim tdf As DAO.TableDef
TableExists = False
For Each tdf In db.TableDefs
If tdf.Name = tableName Then
TableExists = True
Exit For
End If
Next tdf
End Function

Private Function FieldExists(tdf As DAO.TableDef, fieldName As String) As Boolean
Dim fld As DAO.Field
FieldExists = False
For Each fld In tdf.Fields
If fld.Name = fieldName Then
FieldExists = True
Exit For
End If
Next fld
End Function

Private Function GetDataTypeSQLString(fld As DAO.Field) As String
‘ DAOのデータ型定数をJet/ACE SQLのデータ型文字列に変換
Select Case fld.Type
Case dbBoolean: GetDataTypeSQLString = “BIT”
Case dbByte: GetDataTypeSQLString = “BYTE”
Case dbInteger: GetDataTypeSQLString = “SHORT”
Case dbLong: GetDataTypeSQLString = “LONG”
Case dbCurrency: GetDataTypeSQLString = “CURRENCY”
Case dbSingle: GetDataTypeSQLString = “SINGLE”
Case dbDouble: GetDataTypeSQLString = “DOUBLE”
Case dbDate: GetDataTypeSQLString = “DATETIME”
Case dbText: GetDataTypeSQLString = “TEXT(” & fld.Size & “)”
Case dbMemo: GetDataTypeSQLString = “MEMO”
Case dbLongBinary: GetDataTypeSQLString = “LONGBINARY”
Case Else: GetDataTypeSQLString = “TEXT(255)” ‘ フォールバック
End Select
End Function

Private Sub CreateNewTableFromMaster(tdfMaster As DAO.TableDef, dbTarget As DAO.Database)
Dim tdfNew As DAO.TableDef
Dim fldMaster As DAO.Field
Dim fldNew As DAO.Field

‘ 新規テーブル定義のクローン作成
Set tdfNew = dbTarget.CreateTableDef(tdfMaster.Name)

For Each fldMaster In tdfMaster.Fields
Set fldNew = tdfNew.CreateField(fldMaster.Name, fldMaster.Type, fldMaster.Size)
‘ プロパティの移植
On Error Resume Next
fldNew.Required = fldMaster.Required
fldNew.AllowZeroLength = fldMaster.AllowZeroLength
On Error GoTo 0

tdfNew.Fields.Append fldNew
Next fldMaster

dbTarget.TableDefs.Append tdfNew
End Sub

—

4. プロジェクトリーダーからの実践アドバイス：運用時の注意点

このスクリプトを実際のチーム開発・本番運用に組み込むにあたり、以下の鉄則を守ってほしい。

1. 必ずトランザクション（`BeginTrans` / `RollbackTrans`）を張るべし
DDLの途中でエラー（ディスク容量不足や型の不整合）が起きた場合、データベースが半端な状態で壊れる。上記コードのようにトランザクションで囲むことで、失敗時は完全なロールバックが保証される。
2. データ型の変更（ALTER COLUMN）には極めて慎重になるべし
AccessのJetエンジンは、既存データが存在する状態でテキスト型のサイズを縮小したり、長整数型から単精度浮動小数点型へ変更しようとすると、容赦なくエラーを吐くか切り捨てを行なう。実務では「フィールドの追加（ADD）」は自動化し、「既存フィールドの型変更や削除」は手動のマイグレーションスクリプトを別途用意するのが安全である。
3. リンクテーブル更新の自動化
フロントエンド（UI）とバックエンド（データ）が分離しているACCDB構成の場合、バックエンド側のテーブル定義を変更した後は、フロントエンド側の `RefreshLink` メソッドを忘れずに実行させよ。

まとめ

テーブル定義の変更を「なんとなく手動でやる」という悪習から脱却せよ。
VBAによるメタデータ駆動型の差分適用をマスターすれば、デプロイ時のヒューマンエラーはゼロになり、開発スピードは劇的に向上する。

あなたのシステムを、プロフェッショナルな堅牢性で満たしてほしい。