クロス集計クエリの列見出しを動的に制御する：QueryDef動的構築の極限アプローチ
レガシーシステムの寿命は、多くの場合「仕様変更への耐性」によって決まる。特にMicrosoft Accessを用いた業務システムにおいて、設計段階で予測できなかった動的な列挙、例えば「月ごとの売上集計が、四半期ごとや週ごとにスライドする」「動的に追加されるプロジェクトコードが列見出しになる」といった要件に直面したとき、静的なクエリデザインは完全に破綻する。
Accessのクロス集計クエリ（`TRANSFORM` 句）は、列見出しを固定（`PIVOT … IN (…)`）しなければ、データ追加に伴ってフォームやレポートのバインドが崩壊するという致命的なジレンマを抱えている。
今回は、DAOの `QueryDef` オブジェクトをVBAから直接操作し、実行時にSQLを動的に再コンパイル・生成する極限のテクニックを解説する。単なる文字列結合のハックではなく、Jet/ACEエンジンの実行計画キャッシュ、メモリ管理、そしてマルチユーザー環境における競合回避まで踏み込んだアーキテクチャを提示する。
—
1. 静的クロス集計の限界と `QueryDef` の本質
Accessのクエリデザイナで作成するクロス集計は、コンパイル時に列見出しが確定している必要がある。これを動的に行う唯一の現実的かつ堅牢なアプローチが、「永続化された `QueryDef` の `SQL` プロパティをVBAから書き換える」ことだ。
よくある誤ったアプローチとして、DAOの `Recordset` を開くたびに一時的なSQLを投げたり、無駄に `QueryDefs.Append` を繰り返してシステムカタログ（MSysObjects）を肥大化させる開発者がいる。これはAccessのパフォーマンスを著しく低下させ、データベースの破損（Bloat）を誘発する最悪のアンチパターンである。
正解は、「あらかじめ用意したプレースホルダー的なQueryDefのSQLをトランザクション的に書き換え、即座にそれをデータソースとして利用する」ことだ。
—
2. 実装アーキテクチャ：動的TRANSFORM生成エンジン
以下のコードは、指定されたテーブルまたは一時クエリから動的に列挙値（例：部門コードや月度）を抽出させ、`PIVOT` 句の `IN` リストを動的に構築して `QueryDef` のSQLを書き換える実用的なプロシージャである。
Option Compare Database
Option Explicit
‘ ==============================================================================
‘ 担当: チーフアーキテクト
‘ 概要: 指定されたクロス集計用QueryDefのSQLを、動的列見出しに合わせて再構築する
‘ ==============================================================================
Public Sub UpdateCrossTabQueryDef(ByVal qdfName As String, _
ByVal sourceSql As String, _
ByVal transformExpr As String, _
ByVal rowField As String, _
ByVal columnField As String, _
ByVal valueField As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rsCols As DAO.Recordset
Dim colList As String
Dim dynamicSql As String
Set db = CurrentDb()
On Error GoTo ErrorHandler
‘ 1. 動的列見出しのリストを抽出するクエリ（重複排除）
‘ ※パフォーマンス向上のため、対象件数が多い場合はインデックスが必須
Dim sqlCols As String
sqlCols = “SELECT DISTINCT ” & columnField & ” FROM (” & sourceSql & “) WHERE ” & columnField & ” IS NOT NULL ORDER BY ” & columnField
Set rsCols = db.OpenRecordset(sqlCols, dbOpenSnapshot)
‘ 2. PIVOT句のIN条件となる文字列を組み立てる ([Col1], [Col2], [Col3]…)
colList = “”
Do While Not rsCols.EOF
‘ 特殊文字やスペースが含まれる列名に対応するためブラケットで囲む
colList = colList & “, [” & Nz(rsCols.Fields(0).Value, “”) & “]”
rsCols.MoveNext
Loop
rsCols.Close
If Len(colList) > 0 Then
colList = Mid(colList, 3) ‘ 先頭の “, ” を削除
Else
Err.Raise vbObjectError + 1000, “UpdateCrossTabQueryDef”, “動的列見出しの取得に失敗しました。データが存在しません。”
End If
‘ 3. TRANSFORM SQLの骨組みを構築
‘ TRANSFORM [集計値]
‘ SELECT [行項目]
‘ FROM (ソースSQL)
‘ GROUP BY [行項目]
‘ PIVOT [列項目] IN ([動的列リスト]);
dynamicSql = “TRANSFORM ” & valueField & ” ” & _
“SELECT ” & rowField & ” ” & _
“FROM (” & sourceSql & “) ” & _
“GROUP BY ” & rowField & ” ” & _
“PIVOT ” & columnField & ” IN (” & colList & “);”
‘ 4. 対象の QueryDef を取得し、SQLを書き換え
‘ ※あらかじめクエリコンテナにベースとなるQueryDefが存在することが前提
Set qdf = db.QueryDefs(qdfName)
qdf.SQL = dynamicSql
‘ クリーンアップ
GoTo ReleaseAll
ErrorHandler:
MsgBox “クエリ定義の動的生成中に致命的なエラーが発生しました。” & vbCrLf & _
“Error ” & Err.Number & “: ” & Err.Description, vbCritical, “アーキテクチャ例外”
ReleaseAll:
‘ オブジェクトの明示的解放（メモリリーク・ロックの防止）
If Not rsCols Is Nothing Then
rsCols.Close: Set rsCols = Nothing
End If
Set qdf = Nothing
Set db = Nothing
End Sub
—
3. チーフアーキテクトが教える「現場の知見」と最適化の極意
上記のコードを単にコピペするだけでは、プロフェッショナルな現場で通用するシステムとは言えない。以下の3点を必ず押さえておかなければ、マルチユーザー環境やデータ量増大時にシステムが崩壊する。
① Jet/ACEエンジンの実行計画キャッシュとガベージコレクション
`qdf.SQL = dynamicSql` を実行した瞬間、Accessのデータベースエンジン（Jet / ACE）は既存のクエリプランを破棄し、新しいSQLの構文解析と実行計画の再生成を行う。
この操作は高頻度で行うと、システムカタログ領域にゴミ（断片化）を残す原因となる。画面の初期化時やレポート印刷ボタン押下時など、トリガーが明確なタイミング（モーダルな処理）でのみ実行するよう、呼び出し制御を厳格にかけよ。
② メモリリークを断つ「逆順解放の法則」
VBAにおけるDAOオブジェクト（`Database`, `QueryDef`, `Recordset`）の開放順序はパフォーマンスとリソースリークに直結する。
ローカル変数のスコープアウトに頼るのではなく、コードの最後で必ず以下の順序で明示的に `Nothing` を代入せよ。
1. `Recordset` (例: `rsCols.Close: Set rsCols = Nothing`)
2. `QueryDef` (例: `Set qdf = Nothing`)
3. `Database` (例: `Set db = Nothing`)
特に `CurrentDb()` は呼び出すたびに新しいインスタンスをメモリ上に生成するため、プロシージャ内で変数に格納して使い回し、かつ確実に解放することが鉄則である。
③ ネットワーク帯域とSQL Serverバックエンド（ADP代替）の考慮
もしこのAccessが、バックエンドに SQL Server (ODBCリンクテーブル) を抱えている場合、上記の `SELECT DISTINCT columnField FROM …` は、SQL Server側で適切にインデックスが効いていなければ、全件スキャンを引き起こす。
動的列挙の対象となるフィールドには、必ずバックエンド側でインデックス（または非クラスタ化インデックス）を貼らせるべきである。さらに、データ量が多い場合は、VBA側で全件舐めるのではなく、TOP句や期間絞り込みを `sourceSql` の段階で厳格に行うこと。
—
4. 実際のレポート/フォームからの呼び出しパターン
この動的クエリを実際に画面や帳票にバインドする際は、以下のようにコントローラー層から呼び出す。
Private Sub cmdOpenReport_Click()
Dim baseQuery As String
‘ 例: 今年度の実績データを抽出するベースSQL
baseQuery = “SELECT 担当者名, 売上金額, 経過月 ” & _
“FROM T_SalesData ” & _
“WHERE 営業年度 = ” & Me.txtTargetYear.Value
‘ QueryDef “qryCrossTab_Dynamic” のSQLを現在の条件で書き換える
UpdateCrossTabQueryDef _
qdfName:=”qryCrossTab_Dynamic”, _
sourceSql:=baseQuery, _
transformExpr:=”Sum(売上金額)”, _
rowField:=”担当者名”, _
columnField:=”経過月”, _
valueField:=”Sum(売上金額)”
‘ 書き換え完了後、直ちにレポートを開く
‘ レポートの RecordSource には “qryCrossTab_Dynamic” を指定しておく
DoCmd.OpenReport “rptSalesCrossTab”, acViewPreview
End Sub
この設計により、レポートデザインを一切変更することなく、データの変化（例えば「月」から「週」への細分化、あるいは新しいカテゴリの追加）に完全に追従する頑健なクロス集計レポート基盤が完成する。
—
総括
レガシーな技術と見なされがちなAccess VBAであっても、内部構造（DAO, JET/ACEの挙動, オブジェクトライフサイクル）を熟知した上でコードを極限までチューニングすれば、モダンなエンタープライズ要件にも耐えうる堅牢なシステムを構築できる。
「動かない」「遅い」と嘆く前に、クエリの動的制御とメモリの厳格なマネジメントを実装せよ。システムは常に、書いた通りの挙動しかしない。例外なく。
