Access VBAの深淵へ：クロス集計の「列見出し」を自由自在に操る極意
こんにちは。システム開発の現場で、Accessの限界を突破し続けてきたエンジニアです。
Accessを使っていて「クロス集計クエリを作ったけれど、期間やカテゴリが変わるたびに列が消えたり増えたりして、レポートが壊れる…」と頭を抱えたことはありませんか？
多くの初心者がここで挫折し、力技でクエリを何十個も作って管理に疲弊します。しかし、QueryDefオブジェクトを使いこなし、SQLをVBAで動的に書き換える技を覚えれば、そんな悩みは一瞬で霧散します。
今日は、Access VBAの真髄とも言える「クエリの動的生成」の極意を、現場の視点から伝授しましょう。ここを突破すれば、あなたのAccess開発は「作る作業」から「組み立てる作業」へと劇的に進化します。
—
なぜ「QueryDef」なのか？
Accessのクエリは、単なる保存されたデータ抽出命令ではありません。`QueryDef`というオブジェクトを通じて、プログラムから「今、この瞬間の状況に合わせてSQLを書き換える」ことが可能な「生きたプログラム」なのです。
特にクロス集計クエリは、固定のSQLでは柔軟性に欠けます。ここでSQLを直接書き換えることで、どんな動的な要求にも応えられるレポート基盤が完成します。
—
実践：動的列見出しを制御する実装パターン
例えば、売上データを「月別」に集計したいとします。しかし、データがない月は列から消えてしまい、レポートがズレてしまいますよね。これを解決するために、SQLの`PIVOT`句を動的に制御します。
ステップ1：テンプレートとなるクエリを用意する
まず、クエリ名「q_CrossTab_Template」を作成し、SQLビューで以下のようなベースとなるSQLを書いておきます。
（※中身は空でも構いませんが、一度保存しておくことが重要です）
ステップ2：VBAでSQLを差し替える
以下のコードを標準モジュールに記述してください。
Public Sub UpdateCrossTabSQL(strSelectedYear As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
‘ 現在のデータベースを参照
Set db = CurrentDb
‘ 対象のクエリ定義を取得
Set qdf = db.QueryDefs(“q_CrossTab_Template”)
‘ ここが核心：動的にSQLを組み立てる
‘ IN句を使って、強制的に「1月～12月」の列を表示させる
strSQL = “TRANSFORM Sum(T_Sales.Amount) AS SumOfAmount ” & _
“SELECT T_Sales.Category ” & _
“FROM T_Sales ” & _
“WHERE Year([SalesDate]) = ” & strSelectedYear & ” ” & _
“GROUP BY T_Sales.Category ” & _
“PIVOT Format([SalesDate], ‘mm月’) ” & _
“IN (’01月’,’02月’,’03月’,’04月’,’05月’,’06月’,’07月’,’08月’,’09月’,’10月’,’11月’,’12月’);”
‘ QueryDefのSQLプロパティを上書き
qdf.SQL = strSQL
‘ オブジェクトの解放
qdf.Close
Set qdf = Nothing
Set db = Nothing
MsgBox “クエリを” & strSelectedYear & “年版に更新しました！”, vbInformation
End Sub
このコードの「賢い」ポイント
1. `IN`句の魔法: クロス集計の `PIVOT … IN (…)` を使うことで、データが存在しない月であっても、列として強制的に表示させることができます。これがレポートのレイアウト崩れを防ぐ最強の防御策です。
2. DAOの活用: `CurrentDb`から`QueryDefs`を呼び出す手法は、Access VBAの基本でありながら最も高速で安定した手法です。
—
初学者が陥りやすい「落とし穴」
現場でよくある失敗パターンを2つ、共有しておきます。これを知っているだけで、デバッグの時間が半分になります。
1. 「クエリが実行中」というエラー
コード実行時に「クエリが既に開かれています」等のエラーが出る場合、フォームやレポートがそのクエリを参照したまま書き換えようとしています。
解決策: クエリを書き換える際は、必ず参照元のフォームやレポートを`DoCmd.Close`で閉じてから処理を行いましょう。
2. SQLの文字列連結ミス
SQL文の途中で空白を忘れると、`SELECT`のようにコマンドがくっついてしまい、構文エラーになります。
解決策: `& _`（行継続文字）を使う際は、必ず引用符内の前後に半角スペースを入れ、「文字列として正しくスペースが空いているか」をデバッグウィンドウ（Ctrl+G）で`Debug.Print strSQL`して確認する癖をつけてください。
—
さあ、次のステージへ
いかがでしたか？`QueryDef`を操作するということは、Accessという「完成された箱」の中から、SQLという「言語」を直接操ることに他なりません。
「動的に変わる」ということは、ユーザーの「あれも見たい、これも見たい」という要望に、コード1行で応えられるということです。これができるエンジニアは、現場で圧倒的に重宝されます。
まずはこのコードをコピペして、あなたのプロジェクトのクエリ名に書き換えてみてください。そこから先は、あなたの自由な発想次第です。分からないことがあれば、またいつでも聞いてくださいね。応援しています！
