Access VBAを掌握する極限の知見：大規模フラットテーブルをVBAで自動分割・正規化する「移行エンジン」の設計
開発現場でよく直面する悪夢がある。
前任者がExcelの延長で作った、数万から数十万レコードを持つ「全項目入り巨大フラットテーブル」だ。この泥沼化した単一テーブルをリレーショナルデータベースの作法に則って分割し、外部キー（リレーションシップ）を張る――これを手動でやれば、ヒューマンエラーの温床であり、数日を要する不毛な作業となる。
今回は、この大規模なテーブル分割（正規化）とリレーション再構築を、Access VBAのDAO（Data Access Objects）を駆使して完全に自動化する「移行エンジン」の設計手法を伝授する。
綺麗事のコードではない。メモリの枯渇を防ぎ、トランザクションのロールバックを担保し、何十万件ものレコードを高速にさばくための「実践的なプロフェッショナルの知見」をここに公開する。
—
1. なぜ「力技のVBA」では破綻するのか？
愚直な開発者は、すべてのデータをメモリ上に読み込ませたり、単純な `Insert` をループさせたりする。結果として何が起きるか？
- トランザクションログの肥大化とメモリ溢れ: 数十万件のループ処理は、Accessのローカルエンジン（ACE）に甚大な負荷をかけ、エラー 3035（システムリソースの不足）を引き起こす。
- 一意制約（PK/FK）の順序ミス: 親テーブルが存在しない状態で子テーブルにデータを流し込もうとすれば、容赦なくエラー 3205 や 3022 が飛んでくる。
- 部分的な失敗によるデータの不整合: 途中でエラーが起きた際、中途半端に分割されたテーブル群が残り、リカバリ不能に陥る。
我々が目指すべきは、「高速性」「完全なトランザクション管理」「堅牢な型安全」を兼ね備えた、工業製品レベルのデータ移行エンジンである。
—
2. アーキテクチャの全体像と設計方針
今回の移行エンジンは、以下の4ステップを1つのトランザクション（または安全な順序）で実行する。
1. 事前検証 (Validation): 移行元テーブルの存在、重複データの有無、NULL許容フィールドのチェック。
2. DDLによる新スキーマの構築 (Schema Migration): `TableDef` と `Field` オブジェクトをVBAから動的に生成し、主キー（PK）を設定。
3. データ抽出と高速分割ロード (Data Splitting): SQLの `SELECT INTO` または集計クエリ（`GROUP BY`）を駆使し、親マスターを高速生成。その後、外部キーを紐付けながら子テーブルへ展開。
4. リレーションシップの構築 (Relationship Construction): `Relation` オブジェクトをコードで定義し、整合性（参照整合性・カスケード更新）を強制。
—
3. プロダクションコード：自動正規化移行エンジン
以下のコードは、単一のフラットテーブル（`T_OldFlat`：受注明細や顧客情報が全て混ざったテーブル）を、「顧客マスター（T_Master_Customer）」と「受注トランザクション（T_Trans_Order）」に美しく分割・正規化するための実践的モジュールである。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ 模範的データ移行エンジン：フラットテーブルの正規化自動化
‘ =========================================================================
Public Sub ExecuteNormalizationEngine()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim td As DAO.TableDef
Dim rel As DAO.Relation
Dim startTime As Double
startTime = Timer
Set db = CurrentDb()
‘ エラーハンドリングの要：トランザクション開始の準備
‘ ※DAOのトランザクションはJet/ACEエンジンのDDLや一部操作でスコープ外になるため、
‘ ここでは「安全なステップ実行とロールバックの模倣（バックアップ前提）」で解説する。
On Error GoTo ErrorHandler
MsgBox “データ正規化移行エンジンを起動します。” & vbCrLf & _
“処理中はデータベースを操作しないでください。”, vbInformation, “システム通知”
‘ —————————————————————–
‘ Step 1: 新規テーブルスキーマの動的生成 (DDL)
‘ —————————————————————–
Call DropExistingTablesIfExist(db)
‘ 1-1. 顧客マスターの作成
Set td = db.CreateTableDef(“T_Master_Customer”)
With td
.Fields.Append .CreateField(“CustomerID”, dbLong) ‘ 自動採番または一意ID
.Fields.Append .CreateField(“CustomerName”, dbText, 255)
.Fields.Append .CreateField(“CustomerAddress”, dbText, 255)
‘ 主キーの設定
.Indexes.Append .CreateIndex(“PrimaryKey”)
End With
db.TableDefs.Append td
‘ 主キーインデックスの定義（DAO特有の冗長な記述を簡潔に）
Dim idxCustomer As DAO.Index
Set idxCustomer = db.TableDefs(“T_Master_Customer”).Indexes(“PrimaryKey”)
With idxCustomer
.Fields.Append .CreateField(“CustomerID”)
.Primary = True
.Unique = True
End With
db.TableDefs(“T_Master_Customer”).Indexes.Append idxCustomer
‘ 1-2. 受注トランザクションの作成
Set td = db.CreateTableDef(“T_Trans_Order”)
With td
.Fields.Append .CreateField(“OrderID”, dbLong)
.Fields.Append .CreateField(“CustomerID”, dbLong)
.Fields.Append .CreateField(“OrderDate”, dbDate)
.Fields.Append .CreateField(“Quantity”, dbLong)
End With
db.TableDefs.Append td
Dim idxOrder As DAO.Index
Set idxOrder = db.TableDefs(“T_Trans_Order”).Indexes(“PrimaryKey”)
With idxOrder
.Fields.Append .CreateField(“OrderID”)
.Primary = True
.Unique = True
End With
db.TableDefs(“T_Trans_Order”).Indexes.Append idxOrder
‘ —————————————————————–
‘ Step 2: データの分割・流し込み (SQLによるセットベース処理)
‘ —————————————————————–
‘ 【プロの知見】VBAのLoopで1件ずつInsertするのは愚の骨頂。SQLの高速性を利用する。
‘ 2-1. 顧客データの重複排除抽出と投入 (仮にFlatテーブルに CustomerName, Address がある想定)
‘ ※CustomerIDをAutoNumberまたはダミー連番で割り振るため、一時クエリまたはインクリメント処理を入れる
Dim sqlCustomer As String
sqlCustomer = “SELECT DISTINCT T_OldFlat.CustomerName, T_OldFlat.CustomerAddress ” & _
“INTO Temp_UniqueCustomers FROM T_OldFlat WHERE T_OldFlat.CustomerName IS NOT NULL;”
db.Execute sqlCustomer, dbFailOnError
‘ Temp_UniqueCustomers から正式な T_Master_Customer へ ID付きでインサート
‘ （オートナンバー型をマスター側で使う場合の設計）
‘ ここでは簡略化のため、DAOレコードセットによる安全なシーケンス採番インサートを行う
Call InsertMasterData(db)
‘ 2-2. トランザクションデータの投入（マスターのIDをルックアップして結合）
Dim sqlOrder As String
sqlOrder = “INSERT INTO T_Trans_Order (CustomerID, OrderDate, Quantity) ” & _
“SELECT M.CustomerID, F.OrderDate, F.Quantity ” & _
“FROM T_OldFlat F INNER JOIN T_Master_Customer M ” & _
“ON F.CustomerName = M.CustomerName;”
db.Execute sqlOrder, dbFailOnError
‘ —————————————————————–
‘ Step 3: リレーションシップ（参照整合性）のプログラム的構築
‘ —————————————————————–
Set rel = db.CreateRelation(“FK_Customer_Order”)
With rel
.Table = “T_Master_Customer” ‘ 親テーブル
.ForeignTable = “T_Trans_Order” ‘ 子テーブル
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade ‘ カスケード設定
Dim relField As DAO.Field
Set relField = .CreateField(“CustomerID”)
relField.ForeignName = “CustomerID”
.Fields.Append relField
End With
db.Relations.Append rel
‘ —————————————————————–
‘ Step 4: 一時テーブルのクリーンアップ
‘ —————————————————————–
db.Execute “DROP TABLE Temp_UniqueCustomers;”, dbFailOnError
MsgBox “データ正規化移行が正常に完了しました。” & vbCrLf & _
“処理時間: ” & Format(Timer – startTime, “0.00”) & ” 秒”, vbInformation, “完了”
Exit Sub
ErrorHandler:
MsgBox “重大なエラーが発生しました (Error ” & Err.Number & “): ” & vbCrLf & Err.Description, vbCritical, “移行エンジン異常終了”
‘ 実務ではここでロールバック処理やログ出力を行う
End Sub
‘ =========================================================================
‘ 補助プロシージャ：既存移行先テーブルの安全な削除
‘ =========================================================================
Private Sub DropExistingTablesIfExist(db As DAO.Database)
On Error Resume Next
‘ リレーションを先に削除（依存関係エラー回避のため）
db.Relations.Delete “FK_Customer_Order”
db.TableDefs.Delete “T_Trans_Order”
db.TableDefs.Delete “T_Master_Customer”
db.TableDefs.Delete “Temp_UniqueCustomers”
On Error GoTo 0
End Sub
‘ =========================================================================
‘ 補助プロシージャ：マスターデータへの安全なレコード流し込みとID付与
‘ =========================================================================
Private Sub InsertMasterData(db As DAO.Database)
Dim rsSrc As DAO.Recordset
Dim rsDst As DAO.Recordset
Set rsSrc = db.OpenRecordset(“SELECT DISTINCT CustomerName, CustomerAddress FROM Temp_UniqueCustomers”, dbOpenSnapshot)
Set rsDst = db.OpenRecordset(“T_Master_Customer”, dbOpenDynaset)
Do Until rsSrc.EOF
rsDst.AddNew
rsDst!CustomerName = rsSrc!CustomerName
rsDst!CustomerAddress = rsSrc!CustomerAddress
rsDst.Update
rsSrc.MoveNext
Loop
rsSrc.Close
rsDst.Close
End Sub
—
4. チーフアーキテクトが教える、現場で活きる実装の急所
このコードをそのまま現場に投入するにあたり、以下の「プロの知見」を心に留めておいてほしい。
① ループ処理を排除し、セットベースで考える
初心者ほど `T_OldFlat` をレコードセットで1行ずつ読み込み、`DLookup` や `AddNew` でバラそうとする。これでは数万件で数分〜数十分の地獄を見る。上記のコードのように、`INTO` 句や `INSERT INTO … SELECT` といったSQLエンジン（Jet/ACE）の内部最適化に処理を委譲すること。これが高速化の絶対条件だ。
② リレーション削除・作成の順序の罠
データベーススキーマをVBAで変更する際、最も多いバグが「リレーションが結ばれたまま親テーブルを削除しようとしてエラーになる」ケースである。
必ず 「リレーションの削除 ➔ 子テーブルの削除 ➔ 親テーブルの削除」 という逆順のデストラクション（破壊）フェーズを踏まなければならない。補助プロシージャの `DropExistingTablesIfExist` で `On Error Resume Next` を巧妙に使い、初回実行時（テーブルがまだ無い状態）のエラーをいなしている点に注目してほしい。
③ トランザクションとエラーハンドリングの限界を知る
Access VBAにおいて、`DBEngine.BeginTrans` はクエリ（`db.Execute`）によるDDL（テーブル作成やリレーション作成）を完全にロールバックできない仕様上の制約がある。
そのため、本気の大規模移行を行う場合は、「移行実行前にバックアップ（.accdbのファイルコピー）を自動生成するラッパー処理」を必ず手前に実装すること。これがプロとアマを分けるリスクヘッジの思想である。
—
5. おわりに：システムは「美しく」なければ保守できない
泥臭いデータ移行作業こそ、アーキテクトの腕の見せ所だ。
力技でエクセルをこねくり回したり、その場のノリでクエリを作ったりするのではなく、VBAによる厳格なDDLとDMLの制御によって、一瞬にしてフラットな混沌をリレーショナルな秩序へと昇華させる。
このスクリプトをあなたの武器庫に加えれば、どんなに絶望的なレガシーデータと対峙しようとも、冷徹かつエレガントにプロジェクトを勝利へと導くことができるはずだ。
