Access VBAを掌握する極限の知見：CurrentDb.CreateQueryDefで一時クエリを操るスマートなデータ処理
こんにちは！Access VBAの世界へようこそ。
業務自動化の最前線で、AccessとVBAを駆使する皆さん、お疲れ様です。
今日は、Access VBAを次のレベルへと引き上げるための、非常に強力でスマートなテクニックをご紹介します。それは、`CurrentDb.CreateQueryDef` を使って「一時クエリ」を作成し、データベースをクリーンに保ちながら複雑なデータ処理を行う方法です。
マクロの記録から一歩進んで、「もっと自由に、もっと効率的にデータを扱いたい！」と考えているあなたにとって、この記事がAccess VBAの基本をしっかりマスターし、本質を理解するための一助となることを願っています。ここをクリアすれば、Access VBAの基本はバッチリですよ！
—
1. なぜ一時クエリが必要なのか？「データベースを汚さない」開発の第一歩
Accessで業務システムを構築していると、次のような課題に直面することがよくあります。
- 複雑な集計処理: 特定の条件でデータを抽出し、さらに複数の項目でグループ化して合計や平均を出す、といった処理。
- レポートの元データ: レポート表示のためだけに、一時的に複雑な結合や集計が必要な場合。
- データベースの肥大化: 毎回異なる条件のクエリを作成していると、データベースオブジェクト（テーブルやクエリ）がどんどん増えてしまい、管理が大変になる。
- パフォーマンスの低下: 不要なオブジェクトが残ると、データベースの動作が重くなる原因にもなりかねません。
これらの課題をスマートに解決してくれるのが「一時クエリ」です。一時クエリとは、データベースファイルに保存されず、VBAコードの実行中にメモリ上に一時的に作成され、処理が終われば自動的に消滅する、まさに「使い捨て」のクエリのこと。
データベースに痕跡を残さず、必要な時だけ呼び出して使う。この考え方は、クリーンで効率的なシステム開発の基本中の基本なんですよ。
2. Access VBAの心臓部！`Application`と`CurrentDb`オブジェクトを理解する
一時クエリを扱う前に、Access VBAのオブジェクトモデルにおける、非常に重要な二つのオブジェクトについて理解を深めておきましょう。それが `Application` と `CurrentDb` です。
`Application`オブジェクト：Accessという「家」全体を司る
イメージしてみてください。Accessというアプリケーション全体が、まるで一つの「家」のようなものです。
`Application`オブジェクトは、この「家」全体を管理する、いわば「家主」のような存在です。
- Accessアプリケーション自体を操作します（閉じたり、最小化したり、他のオブジェクトを開いたり）。
- Accessのオプション設定にアクセスしたり、VBAエディタを操作したりすることもできます。
例えば、フォームを開く `DoCmd.OpenForm` は、内部的には `Application.DoCmd.OpenForm` と同じ意味で、Accessアプリケーションに指示を出しているわけですね。
`CurrentDb`オブジェクト：今開いている「部屋」に直接アクセスする
一方、`CurrentDb`オブジェクトは、現在開いているデータベースファイルそのものを表します。あなたのAccessアプリケーション（家）の中で、今まさに作業している「部屋」に例えられます。
- この「部屋」の中にある、テーブル、クエリ、フォーム、レポートなどのオブジェクト（コレクション）に直接アクセスできます。
- 新しいテーブルやクエリを作成したり、既存のオブジェクトを削除したりといった、データベースの構造に関わる操作（DDL: Data Definition Language）を行う窓口となります。
なぜ `CurrentDb` が重要なのか？
`CreateQueryDef` メソッドは、新しいクエリ定義オブジェクトをデータベース内に作成するものです。たとえ一時クエリであっても、その「定義」はデータベースのコンテキストの中で行われます。だからこそ、`Application` ではなく、今開いているデータベースそのもの、つまり `CurrentDb` オブジェクトを通じて呼び出す必要があるのです。
「今開いているデータベースに対して、新しいクエリの定義を作ってね」と指示している、と理解すると分かりやすいですね。
3. `CurrentDb.CreateQueryDef` で「一時クエリ」を作成する
さあ、いよいよ本題です。`CurrentDb.CreateQueryDef` メソッドを使って一時クエリを作成し、実行してみましょう。
`QueryDef`オブジェクトとは？
まず、`QueryDef`（クエリ定義）オブジェクトについてです。これは、Accessデータベースに保存されている通常のクエリと同じく、SQLステートメントなどのクエリの定義情報を保持するオブジェクトです。
`CreateQueryDef` メソッドは、この `QueryDef` オブジェクトを新しく作成します。
一時クエリとして使う場合、クエリ名を空文字列（`””`）に指定するのがポイントです。これにより、データベースに保存されず、メモリ上でのみ存在するクエリとして扱われます。
コードで見てみよう！一時クエリの作成・実行・解放
今回は、「特定の期間内に登録された顧客の中から、購入金額が一定以上のお客様の情報を抽出する」という複雑な処理を例に、一時クエリを使ってみましょう。
Option Compare Database
Option Explicit
Sub CreateAndUseTempQuery()
‘ ★ここが極めて重要！オブジェクト変数を宣言します。
‘ QueryDefオブジェクトは一時クエリの定義を格納します。
‘ Recordsetオブジェクトはクエリの結果（データ）を格納します。
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
On Error GoTo ErrorHandler ‘ エラー発生時の処理を定義
‘ ———————————————————————-
‘ 1. 一時クエリのSQLステートメントを定義する
‘ ———————————————————————-
‘ ここでは、架空のテーブル「T_顧客」と「T_注文」を想定しています。
‘ 複数テーブルの結合や集計など、複雑なSQL文を記述できます。
‘ このSQL文は、顧客名、合計購入額、最終注文日を抽出します。
strSQL = “SELECT ” & _
” T_顧客.顧客ID, ” & _
” T_顧客.顧客名, ” & _
” SUM(T_注文.購入金額) AS 合計購入金額, ” & _
” MAX(T_注文.注文日) AS 最終注文日 ” & _
“FROM ” & _
” T_顧客 INNER JOIN T_注文 ” & _
” ON T_顧客.顧客ID = T_注文.顧客ID ” & _
“WHERE ” & _
” T_顧客.登録日 BETWEEN #2023/01/01# AND #2023/12/31# ” & _
“GROUP BY ” & _
” T_顧客.顧客ID, T_顧客.顧客名 ” & _
“HAVING ” & _
” SUM(T_注文.購入金額) >= 10000 ” & _
“ORDER BY ” & _
” SUM(T_注文.購入金額) DESC;”
‘ ———————————————————————-
‘ 2. CurrentDb.CreateQueryDef で一時クエリを作成する
‘ ———————————————————————-
‘ CreateQueryDefメソッドの第一引数に空文字列 (“”) を指定することで、
‘ データベースに保存されない「一時クエリ」が作成されます。
‘ これがポイントです！データベースを汚さずに済みますね。
Set qdf = CurrentDb.CreateQueryDef(“”)
‘ 作成したQueryDefオブジェクトにSQLステートメントをセットします。
qdf.SQL = strSQL
‘ ———————————————————————-
‘ 3. 作成した一時クエリを実行し、結果をRecordsetで取得する
‘ ———————————————————————-
‘ QueryDefオブジェクトのOpenRecordsetメソッドを使って、
‘ クエリの結果をRecordsetオブジェクトとして開きます。
Set rs = qdf.OpenRecordset(dbOpenSnapshot) ‘ dbOpenSnapshotで読み取り専用のスナップショットを開きます
‘ ———————————————————————-
‘ 4. Recordsetのデータを処理する
‘ ———————————————————————-
If Not rs.EOF Then ‘ レコードが存在する場合
rs.MoveFirst ‘ 先頭レコードへ移動
Debug.Print “— 一時クエリの結果 —”
‘ Debug.Print でイミディエイトウィンドウに結果を出力します。
‘ 実際には、ここでレポートに渡したり、別のテーブルに書き込んだり、
‘ フォームのリストボックスに表示したりといった処理を行います。
Do While Not rs.EOF
Debug.Print “顧客ID: ” & rs!顧客ID & _
“, 顧客名: ” & rs!顧客名 & _
“, 合計購入金額: ” & Format(rs!合計購入金額, “#,
0″) & “円” & _
“, 最終注文日: ” & rs!最終注文日
rs.MoveNext ‘ 次のレコードへ移動
Loop
Debug.Print “———————–”
Else
Debug.Print “条件に合致する顧客は見つかりませんでした。”
End If
Exit_Sub:
‘ ———————————————————————-
‘ 5. オブジェクトを解放する（超重要！）
‘ ———————————————————————-
‘ 使い終わったRecordsetオブジェクトを閉じ、オブジェクト変数を解放します。
‘ これを怠ると、メモリやデータベースリソースが解放されず、
‘ システムの動作が不安定になったり、最悪の場合Accessがクラッシュしたりする
‘ 原因となります。オブジェクトのライフサイクルを意識することが、
‘ 安定したシステムを構築する上での「極限の知見」です。
If Not rs Is Nothing Then
If rs.State = adStateOpen Then rs.Close ‘ ADOの場合、DAOでは通常不要だが安全のため
Set rs = Nothing
End If
‘ QueryDefオブジェクトも解放します。
‘ 一時クエリはSet qdf = Nothingとした時点でメモリ上から消滅します。
If Not qdf Is Nothing Then
Set qdf = Nothing
End If
Exit Sub ‘ 正常終了
ErrorHandler:
‘ エラーが発生した場合の処理
MsgBox “エラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“エラー内容: ” & Err.Description, vbCritical
Resume Exit_Sub ‘ オブジェクト解放処理へジャンプ
End Sub
コードの解説とポイント
1. `Dim qdf As DAO.QueryDef`: `DAO.QueryDef` 型のオブジェクト変数を宣言します。DAO (Data Access Objects) はAccessデータベースを操作するためのライブラリです。
2. `Dim rs As DAO.Recordset`: `DAO.Recordset` 型のオブジェクト変数を宣言します。これはクエリの結果として得られるデータを格納するためのものです。
3. `strSQL` の定義: ここに実行したいSQLステートメントを文字列として記述します。複数行にわたる場合は `_` (アンダースコア) で連結することで、コードの可読性を高めることができます。
4. `Set qdf = CurrentDb.CreateQueryDef(“”)`: これが一時クエリを作成する核心部分です。
- `CurrentDb` は現在開いているデータベースを表します。
- `CreateQueryDef` メソッドは新しい `QueryDef` オブジェクトを作成します。
- 引数に `””` (空文字列) を指定することで、Accessのナビゲーションウィンドウに表示されない、つまりデータベースファイルに保存されない一時的なクエリが作成されます。
5. `qdf.SQL = strSQL`: 作成した `QueryDef` オブジェクトの `SQL` プロパティに、先ほど定義したSQLステートメントを設定します。
6. `Set rs = qdf.OpenRecordset(dbOpenSnapshot)`: 定義した一時クエリを実行し、結果を `Recordset` オブジェクトとして開きます。`dbOpenSnapshot` は読み取り専用の静的なデータセットを開くモードで、ほとんどの選択クエリで安全かつ効率的に使えます。
7. `If Not rs.EOF Then … Do While Not rs.EOF … Loop`: `Recordset` のデータを一つずつ処理する典型的なループです。`EOF` (End Of File) は、レコードの終わりに達したかどうかを示します。
8. オブジェクトの解放（超重要！）: `If Not rs Is Nothing Then … Set rs = Nothing` および `If Not qdf Is Nothing Then Set qdf = Nothing` の部分です。
- 使い終わった `Recordset` と `QueryDef` オブジェクトは、必ず `Set オブジェクト変数 = Nothing` として明示的に解放してください。
- これを怠ると、メモリ上にオブジェクトが残り続け、リソースを消費し続けます。特に繰り返し処理の中で解放を忘れると、あっという間にメモリ不足に陥り、Accessアプリケーションの動作が不安定になったり、最悪の場合クラッシュしたりする原因となります。これは、単なる「お作法」ではなく、安定したシステムを構築するための絶対的なルールです。
- 一時クエリの場合、`Set qdf = Nothing` とした時点で、そのクエリ定義はメモリ上から完全に消滅します。データベースに痕跡を残すことはありません。
実際に動かす準備
上記のコードを動かすには、以下の架空のテーブルが必要です。
テーブル名: T_顧客
| フィールド名 | データ型 | 説明 |
| :———– | :——- | :— |
| 顧客ID | オートナンバー | 主キー |
| 顧客名 | 短いテキスト | |
| 登録日 | 日付/時刻 | |
テーブル名: T_注文
| フィールド名 | データ型 | 説明 |
| :———– | :——- | :— |
| 注文ID | オートナンバー | 主キー |
| 顧客ID | 数値 | T_顧客の顧客IDへの外部キー |
| 注文日 | 日付/時刻 | |
| 購入金額 | 通貨 | |
これらのテーブルをAccessデータベース内に作成し、適当なサンプルデータをいくつか入れてから、上記のVBAコードを実行してみてください。イミディエイトウィンドウ（VBAエディタで `Ctrl + G` で開けます）にクエリ結果が表示されるはずです。
4. よくある落とし穴と注意点
1. オブジェクトの解放忘れ
これは先ほども強調しましたが、本当に重要です。特に複雑な処理で複数のオブジェクトを扱う場合、どのオブジェクトをいつ解放すべきか、意識的にコードを記述しましょう。`On Error GoTo` を使って、エラー発生時にも必ず解放処理が実行されるようにする工夫も大切です。
2. SQLステートメントのエラー
SQL文の記述ミスはVBAのデバッグでは見つけにくいことがあります。
- デバッグのヒント: `Debug.Print strSQL` をコードの途中に挿入し、イミディエイトウィンドウにSQL文を出力してみてください。そのSQL文をAccessのクエリデザインビュー（SQLビュー）に直接貼り付けて実行し、エラーが出ないか確認すると、素早く問題を特定できます。
3. パフォーマンスの考慮
一時クエリは非常に便利ですが、非常に大量のデータを扱う場合、SQLの最適化は依然として重要です。インデックスの設定、適切なテーブル結合、必要最低限のデータ抽出など、基本的なデータベース設計の知識も並行して学んでいきましょう。
5. まとめと次のステップ
今回は、`CurrentDb.CreateQueryDef` を使って一時クエリを作成し、データベースをクリーンに保ちながら複雑なデータ処理を行う方法を学びました。
- `Application` と `CurrentDb` オブジェクトの違いと、`CurrentDb` を使う理由を理解しました。
- 名前を空文字列にすることで、データベースに保存されない一時クエリが作成できることを知りました。
- `QueryDef` オブジェクトと `Recordset` オブジェクトの基本的な使い方、そして何よりオブジェクトの適切な解放の重要性を学びました。
これであなたは、Access VBAでのデータ処理を一段上のレベルへと引き上げることができたはずです。データベースを「汚さない」という意識は、保守しやすく、安定したシステムを開発するための基盤となります。
次のステップへの扉
この一時クエリのテクニックをマスターしたら、次はさらに応用的な知識に挑戦してみましょう。
- パラメータクエリ: ユーザーが入力した値などをSQL文に組み込む方法。
- トランザクション処理: 複数の更新処理をまとめて実行し、途中でエラーが発生したらすべてを元に戻す（ロールバック）ことでデータの一貫性を保つ方法。
- エラーハンドリングの強化: `On Error GoTo` をさらに細かく制御し、より堅牢なシステムを構築する方法。
これらの知識を深めていけば、あなたはAccess VBAの真の力を引き出し、どんな複雑な業務要件にも対応できるチーフアーキテクトへと成長していけるでしょう。
頑張ってくださいね！あなたの業務自動化の旅を、心から応援しています。
