Access VBAを掌握する：クロス集計の「列固定」という悪夢を動的SQLで突破する
業務システム開発において、クロス集計クエリは諸刃の剣だ。集計結果を直感的に表示できる一方で、Accessのクロス集計クエリ（TRANSFORM文）は、列見出しが動的に変化すると、レポートのコントロールソースとの紐付けが瞬時に崩壊する。
「期間を絞り込むたびにレポートの列がズレる」「動的列名に対応できずエラーが多発する」。これらは、QueryDefを設計図としてではなく、単なる「静的な保存場所」として扱っていることによる設計上の敗北だ。
今回は、QueryDefをメタプログラミングの対象として捉え、SQLを動的に書き換えることで、堅牢かつ柔軟なレポート基盤を構築する手法を伝授する。
—
1. なぜ「パラメータークエリ」では不十分なのか
多くの初学者は、クロス集計の列見出しに `[Forms]![MyForm]![txtDate]` のようなパラメーターを埋め込もうとする。しかし、Accessのクロス集計において列見出し（PIVOT句）にパラメーターを指定することは、パフォーマンスを著しく低下させるだけでなく、列の並び順の不確定性という致命的なバグを招く。
プロとして採るべきアプローチは「クエリ定義のSQLそのものを、実行直前に書き換える（Refreshする）」という戦略だ。
—
2. 堅牢な設計：動的SQL生成のアーキテクチャ
QueryDefを書き換える際は、以下の3点を徹底しなければならない。
1. テンプレートの保持: ベースとなるSQLを定数または非表示のクエリ定義として保持する。
2. インジェクション対策: SQL文字列を組み立てる際は、`Replace`関数による文字列置換を慎重に行う。
3. 状態のクリーンアップ: 実行後は必ずクエリ定義を破棄、または初期化する。
実装コード：動的クロス集計生成エンジン
以下のコードは、レポートのデータソースとなるクエリを、指定された期間に合わせて自動生成するプロシージャである。
Public Sub UpdateCrosstabQuery(ByVal targetQueryName As String, ByVal startDate As Date, ByVal endDate As Date)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
‘ SQLのテンプレート（PIVOT句のIN節を動的に生成するためのプレースホルダーを用意）
‘ ここでは “2023-01”, “2023-02” などの列見出しをカンマ区切りで生成する想定
Dim pivotHeaders As String
pivotHeaders = GeneratePivotHeaders(startDate, endDate)
Set db = CurrentDb
‘ QueryDefの存在チェックと再作成
On Error Resume Next
Set qdf = db.QueryDefs(targetQueryName)
If Err.Number <> 0 Then
Set qdf = db.CreateQueryDef(targetQueryName)
End If
On Error GoTo 0
‘ SQLの構築
strSQL = “TRANSFORM Sum(T.Amount) AS Total ” & _
“SELECT T.Category FROM MyTable AS T ” & _
“WHERE T.SaleDate BETWEEN #” & Format(startDate, “yyyy/mm/dd”) & “# AND #” & Format(endDate, “yyyy/mm/dd”) & “# ” & _
“GROUP BY T.Category ” & _
“PIVOT Format(T.SaleDate, ‘yyyy-mm’) IN (” & pivotHeaders & “);”
‘ SQLを適用
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
Set db = Nothing
End Sub
‘ 補足：PIVOT句のIN節に渡す文字列を生成するヘルパー関数
Private Function GeneratePivotHeaders(startD As Date, endD As Date) As String
‘ 期間内の年月をクォーテーションで囲ったカンマ区切り文字列を生成するロジック
‘ 例: “‘2023-01’, ‘2023-02’, ‘2023-03′”
‘ 実務ではここをループで構築する
End Function
—
3. プロダクション環境での重要注意点
1. マルチユーザー環境の罠
`CurrentDb` を使用して `QueryDef.SQL` を書き換えると、その変更はデータベース全体に及ぶ。もし複数のユーザーが同時にレポートを閲覧しようとすると、一方がSQLを書き換えている最中に、もう一方がクエリを実行し、データが崩壊するという悲劇が起こる。
- 回避策: フロントエンド（.accdb）をユーザーごとに配布し、クエリ定義をローカルで管理させること。これがAccess開発の鉄則だ。
2. レポートの「コントロールソース」問題
QueryDefを動的に書き換えると、レポートの設計画面で設定した「テキストボックスのコントロールソース」が、フィールド名の不一致によりエラーを起こす。
- 解決策: レポートの `Open` イベントで、各テキストボックスの `ControlSource` をプログラムから動的に再割り当てする。「列見出しが固定されていないレポート」は、設計時にフィールド名で縛ってはいけない。
—
結論：なぜ我々はこれをするのか
クエリを文字列として扱い、動的に生成する行為は、一見すると「汚い」と感じるかもしれない。しかし、変化するビジネス要件に対し、静的なツールで対抗しようとする姿勢こそが、最も技術的負債を蓄積させる。
「クエリはデータである」。
この視点に立ったとき、Access VBAは単なるマクロ言語から、動的なデータ駆動型システムを構築する強力なアーキテクチャへと進化する。設計の堅牢さを犠牲にせず、最大限の柔軟性を確保せよ。それが、Accessで複雑な業務を回し切るための唯一の道だ。
