Access VBAを掌握する極限の知見：DAO.Indexによる一時テーブル高速化の真髄
数百万レコードを抱える基幹システムのデータを、Accessのローカル処理に引き込んでバッチ処理を行う。その際、一時テーブル（TempTable）に対してガリガリと`DLookup`や非効率な`FindFirst`を走らせ、プログレスバーが完全にフリーズしたかのような絶望的な待ち時間に直面したことはないだろうか。
「Accessは遅い」のではない。インデックスの不在という致命的な怠慢が、Jet/ACEエンジンのオプティマイザを殺しているのだ。
今回は、DAO（Data Access Objects）の `Index` オブジェクトをVBAから完全に制御し、コードベースで動的にインデックスを構築・破棄することで、大量データ処理の検索時間を物理的限界まで削ぎ落とすプロフェッショナルな最適化手法を解説する。
—
1. なぜ「一時テーブルのインデックス」がレガシーシステムの救世主となるのか
数万行を超える一時テーブルに対して、インデックスなしでレコードを検索・結合（JOIN）すると、Jet/ACEデータベースエンジンは全表スキャン（Table Scan）を強制される。メモリ上に載っていようとも、O(N)の計算量はデータ量の増加に対して直線的に処理時間を悪化させる。
ここで問題になるのが、Accessにおけるインデックスの挙動の特殊性だ。
リンクテーブル（SQL ServerやOracleなど）や永続的なテーブルであれば、あらかじめデザインビューでインデックスを貼っておけばよい。しかし、実行時に動的に生成するローカルの一時テーブルにおいて、毎回クエリデザイナやGUIを介すことはできない。また、インデックスは検索を爆発的に高速化する一方で、レコードの挿入（INSERT）時にはツリー構造の再構築コストが発生するため、データ投入時はインデックスが存在しない方が圧倒的に速いという二面性を持つ。
この矛盾を解決する唯一の解が、「データ投入 ➔ VBAによる動的インデックス付与 ➔ 高速検索・更新 ➔ 破棄」というオブジェクトライフサイクルの完全制御である。
—
2. アーキテクチャの核心：DAO.Indexの動的生成メカニズム
ADO（ActiveX Data Objects）ではなく、なぜDAOを使うのか？
答えは単純明快である。DAOこそがAccess（Jet/ACE）のネイティブエンジンであり、オブジェクトモデルのオーバーヘッドが最も少なく、DDLの解釈速度が最速だからだ。
DAOでインデックスを動的に付与するには、以下のオブジェクト階層を正確に理解し、適切にメモリ解放を行う必要がある。
1. `CurrentDb()` から対象の `TableDef` を取得する。
2. `TableDef.CreateField` ではなく、`TableDef.CreateIndex` を使用してインデックス定義を作成する。
3. 作成したインデックスに対して `CreateField` でキーとなるフィールドを追加する。
4. `TableDef.Indexes.Append` でデータベースに永続化（物理適用）する。
この一連の操作において、オブジェクトの参照を解放し忘れると、ACEエンジンの内部ロックやメモリリークを引き起こし、長期間稼働するバッチ処理において致命的な「リソース不足」エラーを誘発する。シニアエンジニアであれば、全てのDAOオブジェクト変数を明示的に `Nothing` に代入する作法を絶対に忘れてはならない。
—
3. 【実装コード】極限まで最適化されたインデックス制御モジュール
実際の現場でそのままコピー＆ペーストして即座に組み込める、プロダクション品質のVBAコードを提示する。このコードは、一時テーブルの作成、データのバルクインサート（今回はシミュレーション）、インデックスの動的付与、そして高速検索の一連の流れを完璧なエラーハンドリングと共にカプセル化している。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ 模範的プロフェッショナル・モジュール: DAO Index Optimization Engine
‘ =========================================================================
Public Sub ExecuteHighSpeedTempTableProcessing()
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim startTime As Double
startTime = Timer
Set db = CurrentDb()
‘ 1. 既存の一時テーブルが存在する場合はクリーンアップ
On Error Resume Next
db.TableDefs(“tmp_PerformanceTest”).Name ‘ 存在確認の便宜的なトラップ
If Err.Number = 0 Then
db.Execute “DROP TABLE tmp_PerformanceTest;”, dbFailOnError
End If
On Error GoTo ErrorHandler
‘ 2. 一時テーブルの作成 (DDL)
‘ ※この時点ではインデックスは一切貼らない（INSERTの速度を最大化するため）
db.Execute “CREATE TABLE tmp_PerformanceTest (” & _
“ID LONG, ” & _
“CustomerCode TEXT(20), ” & _
“TransactionDate DATETIME, ” & _
“Amount CURRENCY);”, dbFailOnError
‘ 3. 大量データの高速挿入（トランザクション制御の徹底）
db.BeginTrans
Set rs = db.OpenRecordset(“tmp_PerformanceTest”, dbOpenTable)
Dim i As Long
For i = 1 To 100000
rs.AddNew
rs!ID = i
rs!CustomerCode = “CUST-” & Format(i Mod 5000, “00000”)
rs!TransactionDate = DateAdd(“d”, -(i Mod 365), Date)
rs!Amount = (i 123) Mod 50000
rs.Update
Next i
rs.Close
db.CommitTrans
Debug.Print “データ投入完了 経過時間: ” & Format(Timer – startTime, “0.00秒”)
‘ =========================================================================
‘ 4. 【核心】DAO.Index を用いたインデックスの動的付与
‘ =========================================================================
Set tdf = db.TableDefs(“tmp_PerformanceTest”)
‘ Indexオブジェクトの生成 (引数: インデックス名)
Set idx = tdf.CreateIndex(“PrimaryKey_Index”)
idx.Primary = False ‘ プライマリキーにする場合は True（テーブルに主キーがない場合のみ有効）
idx.Unique = False ‘ 一意制約を持たせる場合は True
‘ インデックスに含めるフィールドを追加
Set fld = idx.CreateField(“CustomerCode”)
idx.Fields.Append fld
‘ 必要であれば複数フィールドによる複合インデックスも追加可能
‘ Set fld2 = idx.CreateField(“TransactionDate”)
‘ idx.Fields.Append fld2
‘ TableDefのIndexesコレクションに追加して物理適用
tdf.Indexes.Append idx
tdf.Indexes.Refresh
Debug.Print “インデックス付与完了 経過時間: ” & Format(Timer – startTime, “0.00秒”)
‘ =========================================================================
‘ 5. インデックスを活用した高速検索の実行
‘ =========================================================================
‘ dbOpenDynaset でもインデックスは有効に機能するが、Seekを使う場合は dbOpenTable を使用する
Set rs = db.OpenRecordset(“tmp_PerformanceTest”, dbOpenTable)
rs.Index = “PrimaryKey_Index” ‘ 使用するインデックスを指定
‘ Seekメソッドによる超高速検索（O(log N)のオーダー）
rs.Seek “=”, “CUST-01234”
If Not rs.NoMatch Then
Debug.Print “検索ヒット! ID: ” & rs!ID & ” / 金額: ” & rs!Amount
Else
Debug.Print “該当データなし”
End If
‘ 終了処理
rs.Close
GoTo CleanUp
ErrorHandler:
If Not db Is Nothing Then
‘ トランザクション中のエラーであればロールバック
On Error Resume Next
db.Rollback
End If
MsgBox “予期せぬエラーが発生しました: ” & Err.Description, vbCritical, “致命的エラー”
CleanUp:
‘ 徹底的なメモリ解放（オブジェクト参照の破棄）
‘ ※これを怠るとAccessのメモリフットプリントが肥大化し、プロセスが不安定になる
If Not fld Is Nothing Then Set fld = Nothing
If Not idx Is Nothing Then Set idx = Nothing
If Not tdf Is Nothing Then Set tdf = Nothing
If Not rs Is Nothing Then Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing
Debug.Print “全処理終了 総合経過時間: ” & Format(Timer – startTime, “0.00秒”)
End Sub
—
4. シニアエンジニアが押さえるべき「極限の知見」と実務の罠
上記のコードを実務の巨大なシステムに組み込む際、さらに一段上のレベルに引き上げるための知見を共有する。
① Jet/ACEエンジンのオプティマイザと「統計情報」の罠
DAOでインデックスを追加した直後、Accessの内部エンジンは即座に新しいインデックスの統計情報（分布状況）を完全に把握しきれていない場合がある。数百万行を一気にインサートした直後にインデックスを貼る場合、`DBEngine.CompactDatabase` をかけるわけにはいかないため、クエリで使用する際は `FORCE INDEX` のようなヒント句が使えないAccessにおいて、オプティマイザが確実にそのインデックスを選ぶようにクエリ構造を工夫する必要がある（例えば、不要な演算をWHERE句の左辺に置かないなど）。
② `dbOpenTable` と `Seek` メソッドの圧倒的なアドバンテージ
SQL文（`SELECT FROM … WHERE …`）を発行するアプローチも強力だが、インデックスを最大限に活かして1件〜数件のレコードをピンポイントで引き抜く場合、`dbOpenTable` で開いたレコードセットに対して `rs.Index = “…”` を設定し、`rs.Seek` を叩く手法は、SQLのパースコストすらもバイパスするAccess VBAにおける究極の高速検索手段である。ODBC経由の外部データベースでは使えない、ローカルDAOならではの特権を使い倒せ。
③ マルチユーザー環境（コンカレンシー）における一時テーブルの設計
本稿で紹介した一時テーブルの動的インデックス生成は、ローカルデータベース（`CurrentDb`）内のプライベートな一時テーブルであれば競合しない。しかし、共有バックエンド（LDF/MDB）上に同名の一時テーブルを作ろうものなら、マルチユーザー環境でデッドロックや「他のユーザーが使用中」エラーの温床となる。
複数セッションが並行稼働する環境では、テーブル名に `Environ(“UserName”)` やプロセス固有のGUIDを付与したセッション分離型の一時テーブルを動的生成することが、プロフェッショナルとしての絶対条件である。
—
5. 結びにかえて
レガシーシステムと揶揄されがちなMicrosoft Accessであるが、その内部でうごめくDAO/Jetエンジンは、適切に調教さえすれば、ローカル処理において驚異的なパフォーマンスを発揮する。
「遅い」と嘆いてクエリを書き換える前に、インデックスのライフサイクルをコードで完全に掌握せよ。ハードウェアの性能に頼るのではなく、アーキテクチャの妙でボトルネックをねじ伏せることこそが、真のプロフェッショナルエンジニアの仕事である。
