Access VBAを掌握する極限の知見：QueryDefの再帰的生成による階層データ抽出の極意
組織図、部品表（BOM）、勘定科目体系。業務システムにおいて「階層データ（ツリー構造）」の扱いは、避けて通れない最大の難所の一つである。
RDB（Relational Database）の本質は集合演算であり、自己参照構造を持つデータを一撃で再帰的に引き出すには、本来はCTE（Common Table Expressions：`WITH RECURSIVE`）のようなモダンなSQL構文が必要だ。しかし、Jet/ACE（Accessのデータベースエンジン）の古いSQL方言は、これをネイティブでサポートしていない。
愚直な開発者は、ここでDAOのレコードセットをループさせ、VBAのメモリ上で何重ものネスト（多重ループ）を組む。結果としてネットワークとCPUを焼き尽くし、「Accessは遅い」という定説を再生産する。
違う。遅いのはAccessではなく、リソースのライフサイクルを無視した稚拙なコードだ。
今回は、Access VBAの隠された実力を極限まで引き出し、`QueryDef` の動的生成・再帰的構築によって、爆速かつエレガントに階層データを抽出するアーキテクチャを提示する。
—
1. なぜ「動的QueryDefの再帰」なのか？
パフォーマンスのボトルネックを排除するため、我々は以下の鉄則を遵守しなければならない。
1. Jet/ACEクエリエンジンへのオフロード: ループ内で `CurrentDb.OpenRecordset` を乱発し、VBA側でレコードを1件ずつ判定するアプローチは、Jetエンジンの最適化プロセッサ（Query Optimizer）を殺す。SQLとして一括処理、あるいは最適化されたパススルー・一時クエリとしてエンジンに実行させるべきだ。
2. クエリDefs（QueryDefs）コレクションの肥大化対策: 動的SQLを生成する際、名前なしの `QueryDef` を安易に永続作成すると、データベースファイル（.accdb）のシステムテーブルがゴミで溢れ、パフォーマンスが急激に劣化する。明示的な作成と即時破棄（または一時クエリとしての再利用）が不可欠である。
動的SQLを文字列結合で組み立て、それを一時的な `QueryDef` オブジェクトとしてコンパイル・実行し、得られた子階層のID群を親の条件にフィードバックする。このプロセスを再帰的に回すことで、SQLインジェクションのリスクを排除しつつ、深さの不定なツリー構造を美しく削り出すことが可能になる。
—
2. 実装アーキテクチャ：再帰的QueryDef抽出エンジン
以下のコードは、親ID（例: 部署IDや部品ID）を起点に、指定した階層（あるいは末端まで）の全子孫レコードを効率的に一括抽出する実用モジュールである。
実現場での耐障害性を考慮し、オブジェクトの解放（`Set … = Nothing`）と、循環参照（ループによる無限再帰）を防ぐためのハッシュ（Dictionary）による安全装置を実装している。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ 階層データ抽出エンジン (Recursive QueryDef Engine)
‘ =========================================================================
Public Sub ExecuteRecursiveTreeExtraction(ByVal rootId As Long, ByVal tableName As String, ByVal idField As String, ByVal parentField As String)
Dim db As DAO.Database
Dim qdTemp As DAO.QueryDef
Dim rs As DAO.Recordset
Dim visited As Object ‘ 循環参照防止用 Dictionary
Dim targetIds As String
Dim nextIds As String
Dim level As Long
On Error GoTo ErrorHandler
Set db = CurrentDb
Set visited = CreateObject(“Scripting.Dictionary”)
‘ 初期ルートIDの設定
targetIds = CStr(rootId)
level = 0
Debug.Print “=== 階層抽出開始 (Root ID: ” & rootId & “) ===”
‘ 階層がなくなるか、安全限界（例: 50階層）に達するまでループ
Do While Len(targetIds) > 0 And level < 50
level = level + 1
nextIds = ""
' 1. 動的QueryDefの構築
' 既存の同名一時クエリがあれば削除（クエリコンテナの汚染を防ぐ）
On Error Resume Next
db.QueryDefs.Delete "_tmp_RecursiveQuery"
On Error GoTo ErrorHandler
' IN句を使った動的SQLの生成（パラメータ数によるJetの制限を考慮し、値埋め込みまたはパラメータ化）
Dim sql As String
sql = "SELECT [" & idField & "], [" & parentField & "] FROM [" & tableName & "] " & _
"WHERE [" & parentField & "] IN (" & targetIds & ");"
Set qdTemp = db.CreateQueryDef("_tmp_RecursiveQuery", sql)
' 2. クエリの実行と結果の処理
Set rs = qdTemp.OpenRecordset(dbOpenSnapshot)
Dim currentBatchIds As String
currentBatchIds = ""
Do Until rs.EOF
Dim currentId As String
currentId = CStr(rs.Fields(idField).Value)
' 循環参照（無限ループ）の検知
If Not visited.Exists(currentId) Then
visited.Add currentId, True
' 次の階層の検索対象IDリストを構築
If Len(currentBatchIds) > 0 Then
currentBatchIds = currentBatchIds & “,” & currentId
Else
currentBatchIds = currentId
End If
‘ 【実務ログ】ここで取得レコードに対するビジネスロジックを展開可能
‘ Debug.Print “Level ” & level & ” : ID = ” & currentId
End If
rs.MoveNext
Loop
‘ リソースの即時解放
rs.Close
Set rs = Nothing
db.QueryDefs.Delete “_tmp_RecursiveQuery”
Set qdTemp = Nothing
‘ 次のループへの引き継ぎ
targetIds = currentBatchIds
Debug.Print “Level ” & level & ” 完了. 発見件数: ” & IIf(Len(targetIds) > 0, UBound(Split(targetIds, “,”)) + 1, 0)
Loop
Debug.Print “=== 階層抽出終了 (総ノード数: ” & visited.Count & “) ===”
CleanUp:
‘ 厳格なオブジェクト解放
On Error Resume Next
If Not rs Is Nothing Then rs.Close: Set rs = Nothing
If Not qdTemp Is Nothing Then db.QueryDefs.Delete “_tmp_RecursiveQuery”: Set qdTemp = Nothing
Set visited = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
MsgBox “エラーが発生しました: ” & Err.Description, vbCritical, “Recursive Engine”
Resume CleanUp
End Sub
—
3. シニアエンジニアが押さえるべき「極限の知見」
上記のコードは単なる「動的SQLのサンプル」ではない。現場の修羅場をくぐり抜けてきたアーキテクトが組み込むべき、いくつかの防衛的設計が施されている。
A. クエリコンテナ（SysObjects）の肥大化とフラグメンテーション防止
Accessでは、VBAから `CurrentDb.CreateQueryDef` を呼ぶと、内部的にデータベースのシステムテーブルにレコードが追加される。これをループや再帰の中で名前を固定せずに乱発すると、`.accdb` ファイル内部の断片化（Bloat）が急速に進み、ファイルサイズが肥大化してパフォーマンスが崩壊する。
上記のコードでは、必ずループの各イテレーションの最初と最後に `db.QueryDefs.Delete` を明示的に実行し、システムカタログを汚染しない設計を徹底している。
B. DAO Snapshotの選択によるメモリ・ロック最適化
`OpenRecordset` を実行する際、デフォルトの `dbOpenDynaset` や `dbOpenTable` を使ってはならない。階層データの抽出は「読み取り専用の集合演算」であり、編集ロックや更新可能チェックのオーバーヘッドは不要である。
必ず `dbOpenSnapshot` を指定し、Jetエンジンのロックマネージャにかかる負荷をゼロに抑えよ。これにより、メモリ消費量は最小限になり、スループットが劇的に向上する。
C. 循環参照（Circular Reference）への耐性
組織図の改編ミスや、部品表の登録ミスにより、親と子がループ構造（例: AがBの親であり、BがAの親である）を形成することは、実際の業務システムでは日常茶飯事である。
これを防がずに単純な再帰やループを回すと、スタックオーバーフローまたは無限ループに陥り、システムがフリーズする。`Scripting.Dictionary` を用いた訪問済みノードのハッシュチェックは、レガシーデータを扱う上での必須の安全装置である。
—
4. システム間連携・大規模データへの拡張アプローチ
もし対象とする階層データが数十万件を超え、Accessのローカルエンジン（Jet/ACE）の限界を超える場合はどうするか？
その場合、VBA側でSQLを組み立てるアプローチを捨て、SQL ServerやPostgreSQLなどのRDB側に「CTE（共通テーブル式）を用いたストアドプロシージャ」を実装し、Accessからはそれをパスクエ（Pass-Through Query）経由でキックするアーキテクトとしての判断を下すべきだ。
しかし、スタンドアロンでの運用や、インフラの制約によりSQL Serverを導入できない環境において、今回解説した 「QueryDefのライフサイクル管理を伴う動的バッチ抽出アルゴリズム」 は、Access VBAの限界を突破する最強の武器となる。
小手先のテクニックに頼るな。メモリを支配し、オブジェクトの生成と消滅を完全にコントロールせよ。それこそが、真のプロフェッショナルエンジニアの仕事である。
