【入門編】QueryDefの実行結果を「非連結フォーム」のレコードソースに動的バインドする際の最適化手法 – Access VBA解析バイブル

Access VBAの隠された力：QueryDefと動的SQLで、フォーム表示速度を劇的に改善！

皆さん、こんにちは！Access VBAの世界へようこそ。今回は、Access開発で避けては通れない「パフォーマンス」という壁を、スマートに乗り越えるための強力なテクニックをご紹介します。特に、フォームの表示速度に悩んでいる方、マクロの記録から一歩進んだ開発を目指したい方には、きっと役立つはずです。

「QueryDef」と「動的SQL」、そして「非連結フォーム」のレコードソースへのバインド。この3つのキーワードを組み合わせることで、あなたのAccessアプリケーションは、まるで生まれ変わったかのように軽快に動作するようになるでしょう。

なぜフォームの表示速度は遅くなるのか？～その原因とQueryDefの役割～

Accessでフォームを作成する際、レコードソースとしてテーブルやクエリを指定するのが一般的ですよね。しかし、フォームに表示するデータが多すぎたり、複雑な条件で絞り込んでいる場合、フォームが開くたびに大量のデータが読み込まれ、表示に時間がかかってしまうことがあります。

ここで登場するのが QueryDef です。QueryDefとは、Accessのデータベース内にSQLクエリをオブジェクトとして保存しておく機能です。あらかじめSQL文を定義しておくことで、VBAから簡単にそのクエリを実行できるようになります。

さらに、QueryDefの真価は、動的SQL と組み合わせることで発揮されます。動的SQLとは、実行時にSQL文の一部を動的に生成・変更する手法のこと。例えば、ユーザーが入力した条件に基づいて、表示するデータを絞り込むといった場合に非常に強力です。

そして、今回注目するのが、このQueryDefで生成したSQLの結果を、非連結フォーム のレコードソースに動的にバインドする最適化手法です。

非連結フォームとは？

まず、「非連結フォーム」について軽く触れておきましょう。連結フォームは、レコードソースのテーブルやクエリと直接紐づいており、データの追加・編集・削除が容易なのが特徴です。一方、非連結フォームは、レコードソースが直接設定されておらず、VBAコードを使ってデータを取得・表示・更新します。

一見、連結フォームの方が便利そうに見えますが、非連結フォームには大きなメリットがあります。それは、フォームの表示処理とデータ取得処理を完全に分離できる こと。これにより、フォームのUI部分の描画を高速化し、その後に必要なデータだけを効率的に取得・表示するという、パフォーマンスに優れた設計が可能になるのです。

QueryDefと動的SQLでSQLを最適化する

フォームの表示速度を改善するための第一歩は、「本当に必要なデータだけを、最も効率的な方法で取得するSQL文を作成する」 ことです。ここにQueryDefと動的SQLの出番があります。

1. 必要な列だけをSELECTする

フォームに表示しない列まで取得していませんか？SQL文で `SELECT ` を使うのは楽ですが、パフォーマンスにとっては大敵です。フォームで実際に表示する列だけを明示的に指定しましょう。

悪い例:

SELECT FROM 注文テーブル WHERE 注文日 BETWEEN #2023/01/01# AND #2023/12/31#;

良い例:

SELECT 注文ID, 顧客名, 商品名, 数量, 金額 FROM 注文テーブル WHERE 注文日 BETWEEN #2023/01/01# AND #2023/12/31#;

2. 条件を絞り込む（WHERE句の活用）

`WHERE` 句を効果的に使うことで、不要なレコードの取得を最初から防ぐことができます。特に、インデックスが設定されている列で絞り込むと、検索速度が劇的に向上します。

3. 動的SQLで条件を柔軟に

ユーザーが入力したテキストボックスの値や、コンボボックスで選択された値などを条件に含めたい場合、動的SQLが活躍します。

例えば、顧客名の一部で検索したい場合、以下のようなSQLをVBAで生成します。

Dim strSQL As String
Dim strCustomerName As String

‘ ユーザーが入力した顧客名（例：「株式会社」）
strCustomerName = Me.txtCustomerName.Value

‘ 動的にSQLを生成
strSQL = “SELECT 注文ID, 顧客名, 商品名, 数量, 金額 FROM 注文テーブル WHERE 顧客名 LIKE ‘” & strCustomerName & “%’;”

この `LIKE` 句と `’%` の組み合わせで、指定した文字列で始まるレコードを検索できます。

QueryDefオブジェクトを使ったSQLの生成と実行

では、実際にVBAコードでQueryDefオブジェクトを操作してみましょう。

QueryDefオブジェクトの基本

Access VBAでは、`Database` オブジェクトの `QueryDefs` コレクションを通じて、QueryDefオブジェクトにアクセスします。

• `CurrentDb`: 現在開いているデータベースオブジェクトを取得します。
• `CreateQueryDef(name As String, sqltext As String)`: 新しいQueryDefオブジェクトを作成します。
• `name`: 作成するQueryDefの名前（空文字列 “” にすると、一時的なQueryDefとして扱われます）。
• `sqltext`: QueryDefに設定するSQL文。
• `QueryDefs(name)`: 既存のQueryDefオブジェクトを取得します。
• `QueryDefs.Append(qdf)`: 作成したQueryDefオブジェクトをデータベースに追加します。
• `QueryDef.OpenRecordset(type As RecordsetTypeEnum)`: QueryDefを実行し、結果をRecordsetオブジェクトとして取得します。

VBAコード例：動的SQLでQueryDefを生成し、Recordsetを取得する

ここでは、フォームのテキストボックスに入力された日付範囲で注文データを取得する例を見てみましょう。

‘———————————————————————
‘ 関数名: GetOrderDataByDateRange
‘ 概要: 指定された日付範囲の注文データをQueryDefで取得する
‘ 引数: startDate (Date) – 開始日
‘ endDate (Date) – 終了日
‘ 戻り値: DAO.Recordset – 取得した注文データのRecordset
‘———————————————————————
Function GetOrderDataByDateRange(startDate As Date, endDate As Date) As DAO.Recordset

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String

‘ 現在のデータベースオブジェクトを取得
Set db = CurrentDb

‘ SQL文を動的に生成
‘ ※重要: 日付型は ‘#’ で囲み、文字列型は “‘” で囲むことを忘れないように！
strSQL = “SELECT 注文ID, 顧客名, 商品名, 数量, 金額 ” & _
“FROM 注文テーブル ” & _
“WHERE 注文日 BETWEEN #” & Format(startDate, “yyyy/mm/dd”) & “# AND #” & Format(endDate, “yyyy/mm/dd”) & “#;”

‘ QueryDefオブジェクトを一時的に作成 (名前は空文字列 “”)
‘ これにより、データベースに永続的に保存されません。
Set qdf = db.CreateQueryDef(“”)
qdf.SQL = strSQL

‘ QueryDefを実行し、Recordsetを取得
‘ dbOpenForwardOnly: 順方向への移動のみを許可。最も高速。
‘ dbReadOnly: 読み取り専用。パフォーマンス向上に寄与。
Set rs = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)

‘ QueryDefオブジェクトは不要になったら解放する (ただし、rsに渡した後は自動的に解放される)
‘ Set qdf = Nothing ‘ rsから参照されている間は解放しない方が安全

‘ 取得したRecordsetを返す
Set GetOrderDataByDateRange = rs

‘ データベースオブジェクトも不要になったら解放
Set db = Nothing

End Function

‘———————————————————————
‘ フォームモジュール例:
‘ フォームのボタンクリックイベントなどで呼び出す
‘———————————————————————
Private Sub cmdExecuteQuery_Click()

Dim rsOrders As DAO.Recordset
Dim frm As Form

‘ フォーム自体を非連結フォームとして扱う
Set frm = Me

‘ 開始日と終了日を取得 (例: フォーム上のテキストボックスから)
‘ ※実際には、日付入力コントロールやエラーハンドリングを適切に行ってください。
Dim dtStartDate As Date
Dim dtEndDate As Date

On Error Resume Next ‘ エラー発生時も処理を続行 (エラーハンドリングは別途実装推奨)
dtStartDate = CDate(Me.txtStartDate.Value)
dtEndDate = CDate(Me.txtEndDate.Value)
On Error GoTo 0

‘ 日付が正しく取得できなかった場合は処理を中断
If IsEmpty(dtStartDate) Or IsEmpty(dtEndDate) Then
MsgBox “開始日と終了日を正しく入力してください。”, vbExclamation
Exit Sub
End If

‘ QueryDefを実行してデータを取得
Set rsOrders = GetOrderDataByDateRange(dtStartDate, dtEndDate)

‘ Recordsetが取得できたか確認
If Not rsOrders Is Nothing Then
If Not rsOrders.EOF Then
‘ 非連結フォームのレコードソースにRecordsetをバインド
‘ BindRecordsetメソッドは存在しないため、EOF/BOFプロパティやRecordCountプロパティを
‘ 確認しながら、手動でデータをフォームコントロールに設定するか、
‘ または、一時テーブルにコピーしてからフォームのレコードソースに設定する方法などが考えられます。
‘ ここでは、簡略化のため、一時テーブルへのコピー＆バインドの概念を説明します。

‘ — 実用的な手法：一時テーブルへのコピー —
‘ 取得したRecordsetを一時テーブルにコピーし、そのテーブルをフォームのレコードソースにする
‘ (この例では、一時テーブル作成・データコピーのコードは省略します)
‘ 例:
‘ Dim strTempTableName As String
‘ strTempTableName = “tmp_OrderData”
‘ Call CopyRecordsetToTempTable(rsOrders, strTempTableName)
‘ Me.RecordSource = strTempTableName

‘ — より高度な手法：Recordsetを直接バインド (ADOの場合など) —
‘ DAOのRecordsetを直接フォームのレコードソースとしてバインドする直接的なメソッドは提供されていません。
‘ そのため、通常は上記のように一時テーブルを利用するか、
‘ ADO (ActiveX Data Objects) を使用して、ADO Recordsetを直接バインドする方法が取られます。

‘ — 簡易的な確認 —
‘ Recordsetの内容をデバッグウィンドウに表示してみる
rsOrders.MoveFirst
Do While Not rsOrders.EOF
Debug.Print “注文ID: ” & rsOrders!注文ID & “, 顧客名: ” & rsOrders!顧客名
rsOrders.MoveNext
Loop
MsgBox “データ取得完了！詳細はイミディエイトウィンドウを確認してください。”, vbInformation
Else
MsgBox “指定された期間に該当する注文データはありませんでした。”, vbInformation
End If

‘ Recordsetをクローズして解放
rsOrders.Close
Set rsOrders = Nothing
Else
MsgBox “データの取得に失敗しました。”, vbCritical
End If

‘ データベースオブジェクトも解放
Set db = Nothing

End Sub

コードのポイント:

• `Format(startDate, “yyyy/mm/dd”)`: 日付をSQL文で正しく扱える形式（`YYYY/MM/DD`）に変換しています。
• `dbOpenForwardOnly`, `dbReadOnly`: Recordsetを開く際のオプションです。これにより、不要な機能が無効化され、パフォーマンスが向上します。
• 非連結フォームへのバインド: 上記のコード例では、直接的なバインドの代わりに、Recordsetの内容をデバッグウィンドウに表示する簡易的な確認にとどめています。
• 実用的な方法: 取得したRecordsetの内容を一時テーブルにコピーし、その一時テーブルをフォームのレコードソースに設定するのが一般的です。これにより、フォームは通常のテーブルをソースとするように動作します。
• ADOの活用: より高度な手法として、ADO (ActiveX Data Objects) を使用すると、ADO Recordsetを直接フォームのレコードソースにバインドできる場合があります。ただし、DAOに慣れている場合は、一時テーブル経由の方が理解しやすいかもしれません。

陥りやすいエラーとその回避策

1. SQLインジェクションのリスク:

• 問題: ユーザーからの入力を直接SQL文に埋め込むと、悪意のあるコードが挿入され、データが改ざんされたり、不正にアクセスされたりする危険性があります。
• 回避策:
• パラメータークエリの利用: `QueryDefs.CreateQueryDef` で名前付きのQueryDefを作成し、`Parameters` コレクションを使って値を渡す方法があります。これにより、SQL文とデータが分離され、安全性が高まります。
• 入力値のサニタイズ: ユーザーからの入力をチェックし、危険な文字（`’` や `;` など）を取り除く処理を事前に行います。

2. 日付/数値/文字列の型変換ミス:

• 問題: SQL文に値を埋め込む際に、データ型に応じた正しい書式で指定しないと、エラーが発生したり、意図しない結果になったりします。
• 回避策:
• 日付: `Format()` 関数で `’yyyy/mm/dd’` 形式にし、`#` で囲む。
• 文字列: シングルクォーテーション (`’`) で囲む。必要に応じて、文字列内のシングルクォーテーションを二重シングルクォーテーション (`”`) にエスケープする。
• 数値: そのまま埋め込む。

3. Recordsetの解放忘れ:

• 問題: `rs.Close` や `Set rs = Nothing` を忘れると、メモリリークやリソースの占有につながり、アプリケーション全体のパフォーマンス低下の原因となります。
• 回避策: コードの最後や、不要になった時点で必ず `rs.Close` と `Set rs = Nothing` を実行する習慣をつけましょう。

まとめ：QueryDefと動的SQLでAccess VBAをマスター！

いかがでしたでしょうか？QueryDefと動的SQL、そして非連結フォームを組み合わせることで、Access VBAのパフォーマンスを劇的に向上させる方法を見てきました。

• 必要なデータだけを、効率的に取得するSQLの作成
• QueryDefオブジェクトを使ったSQLの動的な生成と実行
• 非連結フォームへのバインドによるUIとデータ処理の分離

これらのテクニックを習得すれば、あなたのAccessアプリケーションは、より高速で、より快適なユーザー体験を提供できるようになるはずです。

今回ご紹介した内容は、Access VBA開発における「基礎」でありながら、その「本質」を突くものです。ここをしっかりと理解し、実践することで、マクロの記録だけでは得られない、確かな開発力を身につけることができるでしょう。

ぜひ、今回ご紹介したコード例を参考に、ご自身のアプリケーションで試してみてください。きっと、Access VBAの新たな可能性が見えてくるはずです。

これからも、Access VBAの奥深い世界を一緒に探求していきましょう！