【Access VBA極限チューニング】参照整合性の動的制御で大量データインポートを爆速化する設計手法
開発現場でよくある悲劇について話そう。
外部システムから数万件のCSVデータをAccessのローカルテーブルにインポートする。その際、テーブル間に「参照整合性（Cascading等を含む）」が張られている。
この状態で愚直にデータを突っ込むとどうなるか？ Accessは行が1件追加されるたびにインデックスの更新と外部キー制約の検証を走り続けさせ、処理はみるみるうちに重くなり、最終的にはフリーズしたかのような錯覚に陥る。
「なぜ、インポートのたびに制約チェックを走らせるのか？」
答えはノーだ。そんなものは設計の敗北である。
プロのエンジニアであれば、データ投入の瞬間だけリレーションシップ（参照整合性）をプログラムから一時的に爆破（削除）し、全データ投入完了後に秒速で再構築する。このアプローチをとるべきだ。
今回は、Access VBAの`TableDef`および`Relation`オブジェクトを自在に操り、数万件規模のインポートを極限まで高速化する「堅牢かつエレガントな設計と実装」を伝授する。
—
なぜ参照整合性の維持がボトルネックになるのか？
Access（JET / ACEエンジン）は、リレーショナルデータベースとしての整合性を担保するため、テーブル間のリレーションシップに対して厳格なロックと検証を行う。
参照整合性が有効な状態でレコードを追加・更新すると、エンジンは以下のコストを毎レコードごとに支払う。
1. 親テーブル（マスター）への存在確認（インデックススキャン）
2. トランザクションログの肥大化
3. カスケード更新・削除設定がある場合の連鎖処理の準備
数件〜数百件のデータであれば体感できないが、これが1万件、10万件となると、オーバーヘッドは幾何級数的に跳ね上がる。
インポート処理の最適化の鉄則は、「検証コストをプロセスの外に追い出すこと」だ。
—
堅牢な設計アプローチ：3つのステップ
VBAでリレーションシップを動的に操作する場合、以下のライフサイクルを厳守しなければならない。いい加減なコードを書くと、データベースの破損や予期せぬエラー時に制約が消失したままになるリスクがある。
1. 事前バックアップと存在確認: 対象のリレーションが存在するかを安全にチェック。
2. リレーションの削除 (`Delete`): データベースの `Container` / `Relation` コレクションから該当定義を物理的に除去。
3. データ投入: 制約の枷が外れた状態で、限界まで高速なインポートを実行。
4. リレーションの再構築 (`CreateRelation`): 外部キー制約と参照整合性を復元。
5. 例外処理（トランザクション的保護）: 途中でエラーが発生しても、必ずリレーションが復元される構造（`On Error Goto` の徹底）にする。
—
プロダクションコード：高速インポート制御モジュール
実務でそのまま使える、堅牢性とエラーハンドリングを極めたVBAコードを提示する。
ここでは、マスターテーブル `M_商品` とトランザクションテーブル `T_受注` の間に張られたリレーションシップ（例: `FK_受注_商品`）を対象とする。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ 処理名: 参照整合性を一時解除した高速データインポート基盤
‘ 概要: 外部キー制約を一時削除してインポートを実行し、確実に復元する
‘ =========================================================================
Public Sub ExecuteHighSpeedImport()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim relName As String
Dim relFound As Boolean
Dim targetTable As String
Dim pkTable As String
Dim isRelationRemoved As Boolean
‘ — 設定値 —
relName = “FK_受注_商品” D ‘ 解除するリレーション名
targetTable = “T_受注” ‘ 子テーブル
pkTable = “M_商品” ‘ 親テーブル
Set db = CurrentDb()
isRelationRemoved = False
On Error GoTo ErrorHandler
‘ —————————————————————–
Seps 1: リレーションシップの存在確認と一時削除
‘ —————————————————————–
db.Containers(“Relationships”).Reload
relFound = False
For Each rel In db.Relations
If rel.Name = relName Then
relFound = True
Exit For
End If
Next rel
If relFound Then
Debug.Print “[INFO] リレーションシップ ‘” & relName & “‘ を一時解除します。”
db.Relations.Delete relName
isRelationRemoved = True
End If
‘ —————————————————————–
Step 2: 高速データインポート処理（ここに実際のインポートロジックを入れる）
‘ —————————————————————–
Debug.Print “[INFO] 大量データのインポートを開始…”
‘ 【例】トランザクションを開始してバルクインサート風に処理
db.BeginTrans
‘ — ここに実際のインポート処理（SQLの実行やレコードセット操作など）を記述 —
‘ DoCmd.TransferText … などの代わりに高速なINSERT INTO SQLを推奨
Call SimulateBulkInsert(db)
db.CommitTrans
Debug.Print “[INFO] データのインポートが正常終了しました。”
‘ —————————————————————–
Step 3: リレーションシップの再構築
‘ —————————————————————–
If isRelationRemoved Then
Debug.Print “[INFO] リレーションシップを再構築しています…”
Call RestoreRelation(db, relName, pkTable, targetTable)
isRelationRemoved = False ‘ 正常復元完了
End If
MsgBox “データのインポートが完了しました。”, vbInformation, “高速インポート完了”
Exit Sub
ErrorHandler:
‘ 異常終了時のフォールバック
db.Rollback
MsgBox “エラーが発生しました: ” & Err.Description, vbCritical, “致命的エラー”
‘ 万が一、処理途中で中断した場合もリレーションを復旧させる試み
If isRelationRemoved Then
On Error Resume Next
Call RestoreRelation(db, relName, pkTable, targetTable)
If Err.Number <> 0 Then
MsgBox “【警告】リレーションの復元に失敗しました。手動で確認してください: ” & Err.Description, vbExclamation
End If
On Error GoTo 0
End If
‘ Resume Next ‘ デバッグ用
End Sub
‘ =========================================================================
‘ 補助プロシージャ: リレーションの再定義
‘ =========================================================================
Private Sub RestoreRelation(db As DAO.Database, relName As String, pkTable As String, fkTable As String)
Dim relNew As DAO.Relation
Dim fld As DAO.Field
‘ Relationオブジェクトの作成 (親テーブル, 子テーブル)
Set relNew = db.CreateRelation(relName, pkTable, fkTable, dbRelationUpdateCascade)
‘ フィールドの紐付け (例: 商品IDで結合)
Set fld = relNew.CreateField(“商品ID”)
fld.ForeignField = “商品ID”
relNew.Fields.Append fld
‘ 参照整合性 (Enforce Referential Integrity) の再有効化
relNew.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade And dbEnforceIntegrity
‘ コレクションに追加して確定
db.Relations.Append relNew
Debug.Print “[INFO] リレーションシップの再構築が完了しました。”
End Sub
‘ =========================================================================
‘ 模擬インポート処理
‘ =========================================================================
Private Sub SimulateBulkInsert(db As DAO.Database)
‘ 実際の現場ではここに INSERT INTO … SELECT などのSQLを記述する
‘ 例: db.Execute “INSERT INTO T_受注 (…) SELECT …”, dbFailOnError
‘ 処理の遅延をシミュレート（実際にはここに大量データ処理が入る）
Dim i As Long
For i = 1 To 1000
‘ 仮想的な処理
Next i
End Sub
—
現場で絶対に踏んではいけない「地雷」と注意点
この手法は劇的なパフォーマンス向上をもたらすが、一歩間違えるとデータベースを破壊するリスクも孕んでいる。シニアエンジニアとして以下の注意点をチームに徹底してほしい。
1. 「ゴミデータ」の混入に厳重注意
参照整合性を外すということは、「親が存在しない子レコード（孤児レコード: Orphan Records）」の挿入が技術的に可能になるということだ。
もしインポートするCSV側にマスターに存在しない不正な外部キーが含まれていた場合、制約を再設定（`dbEnforceIntegrity`）する瞬間、VBAは容赦なく実行時エラーを吐き、再構築に失敗する。
- 対策: インポート処理の直後、かつリレーションを再構築する前に、必ず「整合性チェッククエリ（不整合データをあぶり出すクエリ）」をVBAから実行し、不正データを排除またはログ出力するフェーズを挟むこと。
2. 排他制御（マルチユーザー環境の罠）
`db.Relations.Delete` や `CreateRelation` は、データベース全体のスキーマロックを要求する。
他のユーザーが同じデータベースを開いてテーブルを参照している、あるいはフォームを開いているだけで、「書き込みロックされています (Error 3042 / 3218)」などの排他エラーが発生する。
- 対策: 大量データインポートを行うバッチ処理は、必ず「完全排他モード（独占的）」で実行するか、誰もアクセスしていない夜間・早朝、あるいはフロントエンド（FE）とバックエンド（BE）が完全に分離された構成のBE側に対してローカルに実行する設計にすること。共有ネットワーク上のBEに対してこれをやると、ネットワーク切断時にデータベースが容易に破損する。
—
チーフアーキテクトからの総括
プログラミングとは、ただ動くコードを書くことではない。「リソースの制約を理解し、ボトルネックを物理的にバイパスするデザイン」を描くことだ。
今回紹介した「参照整合性の動的制御」は、AccessのJET/ACEエンジンの特性を熟知しているからこそ導き出せる、実務に直結する強力な武器である。数時間かかっていた夜間バッチが数秒で終わる快感を、ぜひ君たちの開発現場でも実装して実証してほしい。
