Access VBAを掌握する極限の知見：QueryDefの再帰的動的生成による階層データ（ツリー構造）抽出の極意
業務システムを構築する際、避けて通れないのが「組織図」や「部品表（BOM）」に代表される階層データ（ツリー構造）の処理だ。
リレーショナルデータベース（RDB）において、親IDを持つ自己参照テーブルから「特定のノード配下にある全ての末端レコード」を抽出する処理を、素朴なSQL（単一のクエリや標準のJOIN）だけでやろうとすると限界が来る。階層の深さが不確定だからだ。
SQL Serverであれば `WITH RECURSIVE`（共通CTE）が一発で解決してくれるが、悲しいかな、我らがMicrosoft Access（JET / ACEエンジン）の標準SQLには、この再帰CTEの構文がネイティブ実装されていない。
では、Access VBAでこれをどう解決すべきか。
「レコードセットをループさせて親子をメモリ上で総なめする」という愚行は、データ量が数千件を超えた瞬間にメモリを食い潰し、Jetエンジンのロック機構を破壊してシステムを死に至らしめる。
ここで登場するのが、QueryDef（クエリ定義）をVBAから動的生成し、メモリ上で再帰的に呼び出してSQLを組み立て・実行するアーキテクチャだ。
今回は、Access VBAの限界を突破し、堅牢かつ圧倒的なパフォーマンスで階層データをねじ伏せる極限の知見を授けよう。
—
1. なぜ「静的クエリ」や「力技のループ」では破綻するのか？
多くの開発者が陥るアンチパターンは以下の2つだ。
1. メモリ上での全件走査（VBのDoWhileループ）
テーブルの全レコードをRecordsetで開き、VBA側でツリーを構築していく手法。小規模なら動くが、ネットワーク経由のバックエンド（SQL ServerやSharePoint等）を接続した瞬間、クエリの往復が多発し、砂時計が永遠に回り続ける。
2. 固定のJOIN句による力技
`LEFT JOIN`を5回繋げて「5階層まで対応」とする設計。これでは組織改編や新製品の投入で階層が深くなった途端にシステムが破綻する。
究極の解：QueryDefの動的構築と再帰的SQLインジェクション
真のプロフェッショナルは、「不確定な階層の深さ」を「VBAの再帰呼び出し」によって解決し、発行するSQL自体を動的にQueryDefとしてコンパイル・キャッシュさせる。
AccessのQueryDefオブジェクトは、単なるテキストの置き場所ではない。一度コンパイルされたQueryDefは、ACEエンジンのクエリプランナーによって最適化される。これを動的に書き換えながら再帰的に評価することで、RDBの馬力を最大限に引き出すのだ。
—
2. 堅牢な設計とファイル・データベース連携の注意点
プロダクション環境でこの手法を採用する場合、以下の「地雷」を踏まないための鉄則がある。
- システムの永続クエリ汚染を防ぐ（Temporary QueryDefの活用）
動的クエリを作るたびに `CurrentDb.QueryDefs.Append` を行うと、ナビゲーションウインドウがゴミクエリで埋まり、データベースが肥大化（Bloat）する。必ず名前に `~` で始まる一時クエリを使うか、コンテナ内のセッションスコープで破棄する設計にする。
- 循環参照（デッドロック・無限ループ）のガード
悪意あるデータや登録ミスにより、親が子を指し、子が親を指す「無限ループ（循環参照）」がツリーに含まれている場合、再帰アルゴリズムはスタックオーバーフローを起こす。VBA側で訪問済みIDのハッシュ（あるいはDictionary）を持ち、二重訪問を完全に遮断する防衛コードが必須である。
—
3. 【コピペで動く】プロダクションコード実装例
それでは、実務でそのまま使えるモジュールを提示する。
ここでは、親ID（`ParentID`）と子ID（`ID`）を持つテーブル（例：`M_Organization`）を想定し、指定したルートID配下の全子孫IDを再帰的に収集して一時テーブルに吐き出すエンジンの実装を示す。
‘ ==============================================================================
‘ モジュール名: clsHierarchyExtractor
‘ 概要: QueryDefの動的生成と再帰処理による階層データ抽出エンジン
‘ ==============================================================================
Option Explicit
Private m_TargetTable As String
Private m_IDColumn As String
Private m_ParentIDColumn As String
Private m_VisitedList As Object ‘ 循環参照防止用のDictionary
‘ 初期化
Public Sub Initialize(tableName As String, idCol As String, parentIdCol As String)
m_TargetTable = tableName
m_IDColumn = idCol
m_ParentIDColumn = parentIdCol
Set m_VisitedList = CreateObject(“Scripting.Dictionary”)
End Sub
‘ 外部から呼び出すエントリーポイント
‘ 指定したRootID配下の全子孫IDを一時テーブル(tmp_ResultTree)に出力する
Public Sub ExtractTree(rootId As Long)
Dim db As DAO.Database
Set db = CurrentDb
‘ 訪問済みリストをクリア
m_VisitedList.RemoveAll
‘ 結果格納用の一時テーブルを作成（存在する場合は一旦削除）
On Error Resume Next
db.Execute “DROP TABLE tmp_ResultTree;”, dbFailOnError
On Error GoTo 0
db.Execute “CREATE TABLE tmp_ResultTree (” & m_IDColumn & ” LONG);”, dbFailOnError
‘ 再帰処理の開始
Me.RecurseFetch db, rootId
Set db = Nothing
End Sub
‘ 内部再帰メソッド：QueryDefを動的生成して子孫を探索
Friend Sub RecurseFetch(db As DAO.Database, currentId As Long)
‘ 循環参照チェック（すでに訪問済みのIDならスルー）
If m_VisitedList.Exists(currentId) Then Exit Sub
m_VisitedList.Add currentId, True
‘ 現在のIDを結果テーブルに書き込み
Dim insertSql As String
insertSql = “INSERT INTO tmp_ResultTree (” & m_IDColumn & “) VALUES (” & currentId & “);”
db.Execute insertSql, dbFailOnError
‘ 直下の子要素を取得する動的QueryDefの名前
Const qryName As String = “~tmp_ChildFetcher”
‘ 既存の同名一時QueryDefがあれば削除
On Error Resume Next
db.QueryDefs.Delete qryName
On Error GoTo 0
‘ 動的SQLの構築：直下の子を抽出する
Dim sql As String
sql = “SELECT ” & m_IDColumn & ” FROM ” & m_TargetTable & ” WHERE ” & m_ParentIDColumn & ” = ” & currentId & “;”
Dim qd As DAO.QueryDef
Set qd = db.CreateQueryDef(qryName, sql)
Dim rs As DAO.Recordset
Set rs = qd.OpenRecordset(dbOpenSnapshot)
‘ 子要素が存在する場合、それぞれのIDに対して再帰呼び出しを行う
Do Until rs.EOF
Dim nextId As Long
nextId = rs(m_IDColumn).Value
‘ 再帰の深さに応じたスタックオーバーフローを防ぎつつ、子を深掘り
Me.RecurseFetch db, nextId
rs.MoveNext
Loop
‘ クリーンアップ
rs.Close
Set rs = Nothing
db.QueryDefs.Delete qryName
Set qd = Nothing
End Sub
使い方（イミディエンスウィンドウ等からのテスト実行用ラッパー）
Sub Test_ExtractHierarchy()
Dim extractor As New clsHierarchyExtractor
‘ テーブル名、ID列名、親ID列名を設定して初期化
extractor.Initialize “M_Organization”, “OrgID”, “ParentOrgID”
‘ 例: 組織ID = 10 配下の全階層組織を展開
extractor.ExtractTree 10
MsgBox “階層データの抽出が完了しました。tmp_ResultTreeを確認してください。”, vbInformation
End Sub
—
4. チーフアーキテクトからの実務アドバイス
この実装を見て、「毎回 `db.Execute` でインサートしたり、QueryDefを作ったり消したりするのはオーバーヘッドではないか？」と勘ぐった優秀なエンジニアがいるならば、それは正しい直感だ。
しかし、実務において数千〜数万件レベルの階層を展開する場合、ネットワークやディスクI/Oのボトルネックよりも、「VBAのメモリ管理の破綻」や「不確定な構造への耐性」の方が遥かに脅威となる。
一時テーブル（`tmp_ResultTree`）に一度吐き出す設計にしておけば、抽出された結果を後続の複雑な集計クエリや帳票モジュール（Accessレポート）のフィードソースとして、そのまま安全にバインドできるという圧倒的なメリットが生まれる。
Access VBAというレガシーに縛られた環境であっても、オブジェクトのライフサイクル（QueryDefの生成と破棄）を完全にコントロールし、アルゴリズムの安全弁（循環参照ガード）を噛ませることで、モダンなシステムに劣らない堅牢な処理基盤を構築できる。
現場のボトルネックを論理でねじ伏せろ。健闘を祈る。
