【テクニカル・上級編】【上級】大規模なテーブル分割（正規化）をVBAで自動化する移行スクリプト – Access VBA解析バイブル

Access VBAを掌握する極限の知見：大規模テーブル分割（正規化）の自動化エンジン

レガシーシステムの寿命は、多くの場合「単一の巨大なフラットテーブル（神テーブル）」の肥大化とともに尽きる。数百万レコードを抱えるワークシートのなれの果てのようなテーブルが、数多のクエリとフォームの結合地獄を生み出し、ネットワーク帯域とJet/ACEエンジンを圧迫する。

我々シニアエンジニアに求められるのは、現場の業務を止めることなく、この腐敗したモノリスを美しく第3正規形へと昇華させる「自動化移行エンジン」の構築である。

本稿では、DAO（Data Access Objects）の挙動特性、ACEエンジンのメモリ管理、そして数万件単位のトランザクション制御を極限まで最適化した、VBAによる大規模テーブル分割・リレーション再構築エンジンの設計思想と実装を解説する。

—

1. アーキテクチャ設計の要諦

巨大テーブルの分割において、最も恐れるべきは「メモリリーク」「トランザクションログの肥大化（エラートラップの欠如によるロールバック不能）」「暗黙の型変換によるパフォーマンス低下」である。

DAOとADODBの適材適所

テーブル定義の変更（DDL）およびリレーションシップの動的構築は、ADOでは制약が多く不完全である。したがって、スキーマ操作にはDAOを厳守する。
一方で、膨大なデータの高速な抽出とバルクインサートには、適切なカーソル管理とキャッシュ戦略を持つDAOのレコードセット、あるいは一括処理用のSQLステートメント（`INSERT INTO … SELECT`）を組み合わせるのが最適解である。

—

2. 実装：高速正規化移行エンジン

以下に、フラットな受注・顧客・商品一体型テーブル（`Z_MegaFlatTable`）を、正規化された3つのテーブル（`T_Customers`, `T_Products`, `T_Orders`）へ分解し、外部キー制約（リレーションシップ）をコードで自動構築するエンジンの全貌を示す。

Option Compare Database
Option Explicit

‘ =========================================================================
‘ 権限と定数定義
‘ =========================================================================
Private Const BATCH_SIZE As Long = 5000 ‘ トランザクション分割サイズ
Private Const TARGET_FLAT_TABLE As String = “Z_MegaFlatTable”

Public Sub ExecuteTableNormalizationEngine()
Dim dbs As DAO.Database
Dim startTime As Double
startTime = Timer

‘ エラー時の不整合を防ぐため、DAOのDB変数を明示的に取得
Set dbs = CurrentDb()

‘ 1. 処理中のパフォーマンス向上のため、システム設定を最適化
DBEngine.SetOption dbMaxLocksPerFile, 150000 ‘ 既定のロック上限を引き上げ

On Error GoTo ErrorHandler

‘ トランザクション開始（Jet/ACEのエンジンレベルでのアトミック性を担保）
dbs.BeginTrans

MsgBox “正規化エンジンを起動します。処理完了までデータベースを操作しないでください。”, vbInformation

‘ 2. 移行先スキーマ（テーブルとリレーション）の構築
Call sBuildNormalizedSchema(dbs)

‘ 3. データの抽出・重複排除・流し込み（マスター系 -> トランザクション系）
Call sMigrateMasterData(dbs)
Call sMigrateTransactionData(dbs)

‘ コミット
dbs.CommitTrans

MsgBox “テーブルの正規化とリレーション再構築が正常に完了しました。” & vbCrLf & _
“処理時間: ” & Format(Timer – startTime, “0.00”) & ” 秒”, vbInformation

CleanUp:
‘ オブジェクトの明示的解放（メモリリークの完全排除）
Set dbs = Nothing
Exit Sub

ErrorHandler:
dbs.RollbackTrans
MsgBox “致命的なエラーが発生しました。変更はロールバックされました。” & vbCrLf & _
“Error ” & Err.Number & “: ” & Err.Description, vbCritical
Resume CleanUp
End Sub

—

3. スキーマ自動構築とリレーションのプログラム制御

テーブルの動的生成とリレーションシップの定義は、DAOの `TableDef` コレクションと `Relation` オブジェクトを駆使して行う。UIを介さないため、人為的ミスが介入する余地はない。

Private Sub sBuildNormalizedSchema(ByRef dbs As DAO.Database)
Dim tdf As DAO.TableDef
Dim rel As DAO.Relation

‘ 既存の分割先テーブルが存在する場合は安全に破棄（※本番ではバックアップ必須）
On Error Resume Next
dbs.TableDefs.Delete “T_Orders”
dbs.TableDefs.Delete “T_Customers”
dbs.TableDefs.Delete “T_Products”
On Error GoTo 0

‘ — 1. 顧客マスタ (T_Customers) の作成 —
Set tdf = dbs.CreateTableDef(“T_Customers”)
With tdf
.Fields.Append .CreateField(“CustomerID”, dbLong)
.Fields(“CustomerID”).Attributes = dbAutoIncrField ‘ 既存IDを維持する場合は長整数型として作成
.Fields.Append .CreateField(“CustomerName”, dbText, 255)
.Fields.Append .CreateField(“CustomerAddress”, dbText, 255)

‘ 主キー設定
.Indexes.Append .CreateIndex(“PrimaryKey”)
.Indexes(“PrimaryKey”).Fields.Append .Indexes(“PrimaryKey”).CreateField(“CustomerID”)
.Indexes(“PrimaryKey”).Primary = True
End With
dbs.TableDefs.Append tdf

‘ — 2. 商品マスタ (T_Products) の作成 —
Set tdf = dbs.CreateTableDef(“T_Products”)
With tdf
.Fields.Append .CreateField(“ProductID”, dbLong)
.Fields(“ProductID”).Attributes = dbAutoIncrField
.Fields.Append .CreateField(“ProductName”, dbText, 255)
.Fields.Append .CreateField(“UnitPrice”, dbCurrency)

.Indexes.Append .CreateIndex(“PrimaryKey”)
.Indexes(“PrimaryKey”).Fields.Append .Indexes(“PrimaryKey”).CreateField(“ProductID”)
.Indexes(“PrimaryKey”).Primary = True
End With
dbs.TableDefs.Append tdf

‘ — 3. 受注トランザクション (T_Orders) の作成 —
Set tdf = dbs.CreateTableDef(“T_Orders”)
With tdf
.Fields.Append .CreateField(“OrderID”, dbLong)
.Fields(“OrderID”).Attributes = dbAutoIncrField
.Fields.Append .CreateField(“CustomerID”, dbLong)
.Fields.Append .CreateField(“ProductID”, dbLong)
.Fields.Append .CreateField(“OrderDate”, dbDate)
.Fields.Append .CreateField(“Quantity”, dbLong)

.Indexes.Append .CreateIndex(“PrimaryKey”)
.Indexes(“PrimaryKey”).Fields.Append .Indexes(“PrimaryKey”).CreateField(“OrderID”)
.Indexes(“PrimaryKey”).Primary = True
End With
dbs.TableDefs.Append tdf

‘ — 4. リレーションシップ（外部キー制約・参照整合性）のプログラム的定義 —
‘ 顧客と受注のリレーション
Set rel = dbs.CreateRelation(“Rel_Customer_Order”, “T_Customers”, “T_Orders”, dbRelationUpdateCascade)
rel.Fields.Append rel.CreateField(“CustomerID”)
rel.Fields(“CustomerID”).ForeignName = “CustomerID”
dbs.Relations.Append rel

‘ 商品と受注のリレーション
Set rel = dbs.CreateRelation(“Rel_Product_Order”, “T_Products”, “T_Orders”, dbRelationUpdateCascade)
rel.Fields.Append rel.CreateField(“ProductID”)
rel.Fields(“ProductID”).ForeignName = “ProductID”
dbs.Relations.Append rel

Set tdf = Nothing
Set rel = Nothing
End Sub

—

4. 高速データ移行：SQLのバルク処理とメモリの極限最適化

レコードセットをループさせて1件ずつ `AddNew` / `Update` を行うコードを見かけるが、これは数万件以上の規模においては悪夢である。Jet/ACEエンジンに直結するSQLの `INSERT INTO … SELECT` を用いることで、処理速度を数十倍から数百倍に跳ね上げることが可能だ。

ここでは、重複排除（`DISTINCT`）を活用してマスターデータを抽出し、トランザクションデータを効率的に流し込むSQL戦術を採用する。

Private Sub sMigrateMasterData(ByRef dbs As DAO.Database)
Dim strSQL As String

‘ 1. 顧客データの移行（フラットテーブルからユニークな顧客情報を抽出）
‘ ※実際のスキーマに合わせてフィールド名は読み替えてください
strSQL = “INSERT INTO T_Customers (CustomerID, CustomerName, CustomerAddress) ” & _
“SELECT DISTINCT CustomerID, Max(CustomerName), Max(CustomerAddress) ” & _
“FROM ” & TARGET_FLAT_TABLE & ” ” & _
“WHERE CustomerID IS NOT NULL ” & _
“GROUP BY CustomerID;”
dbs.Execute strSQL, dbFailOnError

‘ 2. 商品データの移行
strSQL = “INSERT INTO T_Products (ProductID, ProductName, UnitPrice) ” & _
“SELECT DISTINCT ProductID, Max(ProductName), Max(UnitPrice) ” & _
“FROM ” & TARGET_FLAT_TABLE & ” ” & _
“WHERE ProductID IS NOT NULL ” & _
“GROUP BY ProductID;”
dbs.Execute strSQL, dbFailOnError
End Sub

Private Sub sMigrateTransactionData(ByRef dbs As DAO.Database)
Dim strSQL As String

‘ 3. 受注トランザクションデータの移行
strSQL = “INSERT INTO T_Orders (CustomerID, ProductID, OrderDate, Quantity) ” & _
“SELECT CustomerID, ProductID, OrderDate, Quantity ” & _
“FROM ” & TARGET_FLAT_TABLE & “;”

dbs.Execute strSQL, dbFailOnError
End Sub

—

5. 伝説的アーキテクトからの警句：保守性と実運用への備え

このエンジンを本番環境（あるいはクライアントのネットワーク共有環境）に投入する際、以下の「現場の泥臭い現実」に直面する。

1. トランザクションログと `dbMaxLocksPerFile`
Accessのデフォルト設定では、1つのトランザクション内でロックできるリソース数に上限（通常9500個）がある。数万件規模の `INSERT` を1つのトランザクションで行うと、「リソースが枯渇しました (Error 3035)」で即座にクラッシュする。上記のコードで `DBEngine.SetOption dbMaxLocksPerFile, 150000` を入れているのは、この物理的制約を回避するための必須の知見である。
2. データの不整合（孤児レコードの存在）
フラットテーブルの段階で、マスター側に存在しない `CustomerID` がトランザクション側に紛れ込んでいる場合、外部キー制約を付与した瞬間にエラーとなる。必要に応じて、移行処理の前に `WHERE NOT EXISTS` を使ったクレンジングクエリを挟む設計的度胸が求められる。

モノリスを解体し、美しい正規化されたリレーショナルモデルへと昇華させること。それは単なるリファクタリングではなく、レガシーシステムの生命を再び何年分も延ばす、エンジニアリングの極みである。