Access VBAの深淵へようこそ！パススルー・クエリでSQL Serverの負荷を劇的に軽減する究極のテクニック
皆さん、こんにちは！業務自動化のチーフアーキテクトです。
AccessとVBAの世界へようこそ。この世界は、皆さんの日々の業務を劇的に変える可能性を秘めています。
「マクロの記録」から一歩踏み出し、「自分でコードを書いて、Accessを意のままに操りたい！」と考えているあなたに、今日はAccess VBAの真髄に触れる、とっておきのテクニックをお伝えしましょう。
今回のテーマは、「DAO.Database.CreateQueryDefでパススルー・クエリを動的に生成し、SQL Serverの負荷を軽減する」 です。
少し難しそうに聞こえるかもしれませんが、ご安心ください。SQL Serverと連携しているAccessユーザーなら、この技術をマスターすれば、あなたのシステムは次のレベルへ進化します。ここをクリアすれば、Access VBAの基本はバッチリですよ！
1. AccessとSQL Serverの連携、その「もったいない」使い方とは？
Accessは、リンクテーブル機能を使ってSQL Serverなどの外部データベースと簡単に連携できますね。これは非常に便利です。しかし、実は多くのAccessアプリケーションが、この連携機能の「真の力」を引き出せていないことが多いのです。
例えば、リンクテーブルを使ってSQL Serverの巨大なテーブルからデータを取得し、Access側で集計やフィルタリングを行っていませんか？
`SELECT FROM リンクテーブル WHERE …`
`SELECT COUNT() FROM リンクテーブル GROUP BY …`
こんなクエリをAccessで実行すると、裏側では何が起きているかご存知でしょうか？
Accessが「頑張りすぎる」と何が起きる？
通常、AccessのクエリデザイナやVBAでリンクテーブルに対してSQLを実行すると、Accessは以下のような手順で処理を進めます。
1. AccessがSQLを「解釈」する: Accessは受け取ったSQL文を一度自分の言葉に翻訳しようとします。
2. SQL Serverから「全データ」または「大量のデータ」を取得する: Accessは翻訳した結果、「うーん、この集計はAccess側でやるとして、まずはSQL Serverさん、このテーブルのデータを全部か、あるいはかなり広い範囲で持ってきてください！」と要求します。
3. ネットワークを介して大量のデータを転送する: SQL Serverはその要求に応え、大量のデータをAccess側に送りつけます。このとき、ネットワーク帯域が圧迫され、ボトルネックになります。
4. Accessが「自力で」集計・フィルタリングする: やっとデータを受け取ったAccessは、そこから必要な行だけを抽出し、集計処理を行います。このとき、Accessを実行しているPCのCPUやメモリがフル稼働します。
想像してみてください。あなたは巨大な倉庫（SQL Server）から、たった10個のリンゴの合計個数を調べたいだけなのに、その倉庫にあるリンゴを全部トラックに積んで自分の家（Access PC）まで運び、そこで一つ一つ数え直しているようなものです。
これは非常に「もったいない」ですよね。
SQL Serverは、もともと大量のデータを高速に処理することに特化した、非常に強力なデータベースです。その力をAccessが「頑張りすぎ」ることで、かえって性能を落としているケースが少なくありません。
2. SQL Serverの真価を引き出す「パススルー・クエリ」とは？
そこで登場するのが、今回の主役「パススルー・クエリ」です！
パススルー・クエリとは、その名の通り、AccessがSQL文を一切解釈・加工することなく、「そのまま（Pass Through）」SQL Serverに投げつけるクエリのことです。
パススルー・クエリのメリット
1. SQL Serverの圧倒的な処理速度を享受: SQL Serverは、データの集計やフィルタリングをAccessよりはるかに高速に実行できます。これにより、クエリ全体の実行時間が大幅に短縮されます。
2. ネットワーク負荷の軽減: AccessはSQL Serverから「処理済みの最終結果」だけを受け取ります。上記の例で言えば、SQL Serverがリンゴを数えて「合計1000個でした」という答えだけをAccessに返すようなものです。これにより、ネットワークを流れるデータ量が劇的に減り、ネットワークが安定します。
3. SQL Serverの高度な機能を利用可能: SQL Server独自の関数や構文、ストアドプロシージャなどをAccessから直接利用できるようになります。
パススルー・クエリのデメリット（注意点）
- AccessはSQLの構文チェックを行わない: SQL Serverに直接SQLを投げるため、SQL文に構文エラーがあってもAccessは警告してくれません。実行時にSQL Serverからエラーが返ってきます。
- ODBC接続文字列が必要: どのSQL Serverに接続するかを示す情報（接続文字列）が必要です。
このパススルー・クエリを、VBAを使って「動的に」生成・実行することで、ユーザーの操作や状況に応じた柔軟なデータ取得が可能になります。
3. DAO.Database.CreateQueryDef でパススルー・クエリを動的に生成する
Access VBAでパススルー・クエリを扱うには、DAO (Data Access Objects) というライブラリを使います。特に重要なのが `QueryDef` オブジェクトと、それを生成する `CreateQueryDef` メソッドです。
`QueryDef` オブジェクトは、Accessのクエリそのものを表します。普段、クエリデザイナで作っている「保存されたクエリ」も、VBAから見れば `QueryDef` オブジェクトの一つです。
そして、`CreateQueryDef` メソッドを使うことで、コード上で「一時的なクエリ」や「新しいクエリ」を生成し、すぐに実行することができます。
今回は、この `CreateQueryDef` を使って、VBAから動的にパススルー・クエリを作成し、SQL Serverで集計させて結果を取得する手順を解説します。
準備：参照設定の確認
まずは、VBAプロジェクトでDAOライブラリが有効になっているかを確認しましょう。
1. AccessのVBAエディタ（Alt + F11）を開きます。
2. メニューバーから「ツール」→「参照設定」を選択します。
3. 表示されるダイアログボックスで、「Microsoft DAO 3.6 Object Library」または「Microsoft Office x.0 Access database engine Object Library」にチェックが入っていることを確認します。
- 通常、Access 2007以降であれば「Microsoft Office x.0 Access database engine Object Library」が推奨されます（xはAccessのバージョンによって異なります）。
- もし両方とも見当たらない場合は、「参照」ボタンをクリックして、`C:\Program Files\Common Files\Microsoft Shared\DAO` (32bit OS) または `C:\Program Files (x86)\Common Files\Microsoft Shared\DAO` (64bit OS) の中の `DAO360.DLL` を探して選択します。
これができていないと、DAOオブジェクトを宣言したときに「ユーザー定義型は定義されていません」というエラーが出ますので注意してくださいね。
4. 実践！パススルー・クエリの動的生成と実行
それでは、具体的なVBAコードを見ていきましょう。
今回は、「SQL Serverの`Products`テーブルから、カテゴリごとの製品数を取得する」というシナリオで進めます。
サンプルコード
Option Compare Database
Option Explicit
‘
‘ プロシージャ名: GetProductCountByCategory_PassThrough
‘ 概要 : SQL ServerのProductsテーブルから、カテゴリごとの製品数をパススルー・クエリで取得します。
‘ : DAO.QueryDefを動的に生成し、SQL Server側で集計処理を実行させ、
‘ : 結果のみをAccessに返却することで、パフォーマンスと負荷軽減を実現します。
‘
Sub GetProductCountByCategory_PassThrough()
‘ DAOオブジェクトの宣言
‘ Databaseオブジェクト: 現在のAccessデータベース（mdb/accdb）を表します。
‘ QueryDefオブジェクト: クエリの定義（SQL文、接続情報など）を保持します。
‘ Recordsetオブジェクト: クエリの実行結果（レコード）を保持します。
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strConnect As String
Dim i As Long ‘ ループカウンタ
On Error GoTo ErrorHandler ‘ エラー処理ルーチンの設定
‘———————————————————————————————-
‘ 1. SQL Serverへの接続文字列を定義します。
‘ DSNレス接続（ODBCデータソースを事前に作成しない方法）が推奨されます。
‘ ご自身の環境に合わせて、Server, Database, UID, PWD を書き換えてください。
‘ Trusted_Connection=Yes はWindows認証の場合です。SQL Server認証の場合はUID/PWDを指定します。
‘———————————————————————————————-
strConnect = “ODBC;DRIVER={ODBC Driver 17 for SQL Server};” & _
“SERVER=YourSqlServerName;” & _
“DATABASE=YourDatabaseName;” & _
“UID=YourUserName;” & _
“PWD=YourPassword;”
‘———————————————————————————————-
‘ 2. SQL Serverで実行させたいSQL文を定義します。
‘ このSQL文はAccessが解釈せず、そのままSQL Serverに送られます。
‘ ここではカテゴリごとの製品数を集計するSQLを記述します。
‘———————————————————————————————-
strSQL = “SELECT ” & _
” CategoryName, ” & _
” COUNT(ProductID) AS ProductCount ” & _
“FROM ” & _
” Products ” & _
“GROUP BY ” & _
” CategoryName ” & _
“ORDER BY ” & _
” CategoryName;”
‘———————————————————————————————-
‘ 3. 現在のAccessデータベースオブジェクトを取得します。
‘ CurrentDbは、現在開いているAccessデータベースへの参照を返します。
‘———————————————————————————————-
Set db = CurrentDb
‘———————————————————————————————-
‘ 4. 新しいQueryDefオブジェクト（パススルー・クエリ）を動的に作成します。
‘ 第1引数: クエリ名。ここでは一時クエリなので空文字列 (“”) を指定。
‘ 第2引数: クエリタイプ。dbSQLPassThroughを指定することで、パススルー・クエリになります。
‘———————————————————————————————-
Set qdf = db.CreateQueryDef(“”, dbSQLPassThrough)
‘———————————————————————————————-
‘ 5. QueryDefオブジェクトのプロパティを設定します。
‘ .Connect: SQL Serverへの接続文字列を設定します。
‘ .SQL: SQL Serverで実行するSQL文を設定します。
‘———————————————————————————————-
qdf.Connect = strConnect
qdf.SQL = strSQL
‘———————————————————————————————-
‘ 6. パススルー・クエリを実行し、結果をRecordsetオブジェクトとして取得します。
‘ OpenRecordsetは、クエリの結果をレコードの集合として開きます。
‘———————————————————————————————-
Set rs = qdf.OpenRecordset()
‘———————————————————————————————-
‘ 7. 取得した結果を表示します。
‘ RecordsetのEOFプロパティで、レコードの終端に達したかを確認します。
‘———————————————————————————————-
If Not rs.EOF Then
Debug.Print “—————————————”
Debug.Print “カテゴリ名” & vbTab & “製品数”
Debug.Print “—————————————”
Do While Not rs.EOF
‘ フィールドは名前でアクセスできます
Debug.Print rs!CategoryName & vbTab & rs!ProductCount
rs.MoveNext ‘ 次のレコードへ移動
Loop
Else
Debug.Print “データが見つかりませんでした。”
End If
Debug.Print “—————————————”
Debug.Print “パススルー・クエリの実行が完了しました。”
Exit_Sub:
‘———————————————————————————————-
‘ 8. オブジェクトを解放します。これが非常に重要です！
‘ 使い終わったオブジェクトは必ずNothingを設定してメモリから解放しましょう。
‘ これにより、リソースリークを防ぎ、システムの安定稼働に貢献します。
‘ 特にDAOオブジェクトは、適切に解放しないとDB接続が残存したり、メモリが消費され続けたりする原因になります。
‘———————————————————————————————-
If Not rs Is Nothing Then
rs.Close ‘ Recordsetを閉じます
Set rs = Nothing
End If
If Not qdf Is Nothing Then
‘ 一時クエリとして生成した場合は、名前がないためDeleteは不要ですが、
‘ 明示的にオブジェクト参照をNothingにするのは良い習慣です。
Set qdf = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If
Exit Sub
ErrorHandler:
‘ エラーが発生した場合の処理
MsgBox “エラーが発生しました: ” & Err.Description, vbCritical, “エラー”
Resume Exit_Sub ‘ エラー処理後、オブジェクト解放ルーチンへジャンプ
End Sub
コードの解説：Access VBAの基礎と本質
一つ一つのステップを丁寧に見ていきましょう。
1. `Dim db As DAO.Database` など:
- これは「変数宣言」です。VBAを使う上で最も基本的なことの一つですね。
- `DAO.Database` は、現在開いているAccessデータベースそのものを扱うための「型」です。
- `DAO.QueryDef` は、クエリの定義（SQL文や接続情報）を扱うための「型」です。
- `DAO.Recordset` は、クエリを実行した結果（データの集まり）を扱うための「型」です。
- `Option Explicit` をモジュールの先頭に記述することで、変数の宣言忘れによるエラーを防ぎ、堅牢なコードを書く習慣が身につきます。
2. `strConnect` の定義:
- ここがSQL Serverに接続するための「住所」と「鍵」です。
- `ODBC;DRIVER={ODBC Driver 17 for SQL Server};`：ODBCを使ってSQL Serverに接続することを宣言し、どのODBCドライバーを使うかを指定します。`{ODBC Driver 17 for SQL Server}`は、ご自身の環境でインストールされているドライバー名に合わせてください（例: `{SQL Server}`、`{ODBC Driver 13 for SQL Server}`など）。
- `SERVER=YourSqlServerName;`：接続先のSQL Serverのインスタンス名またはIPアドレスです。
- `DATABASE=YourDatabaseName;`：接続先のデータベース名です。
- `UID=YourUserName;PWD=YourPassword;`：SQL Server認証を使う場合のユーザーIDとパスワードです。Windows認証を使う場合は `Trusted_Connection=Yes;` とします。
- この接続文字列が正しくないと、SQL Serverに接続できません。最も陥りやすいエラーの一つなので、ご自身の環境に合わせて正確に記述してください。
3. `strSQL` の定義:
- これは、SQL Serverで実行したい純粋なSQL文です。Accessのクエリデザイナで書くSQLとは異なり、SQL Serverの構文規則に完全に準拠している必要があります。
- ここでは `GROUP BY` を使って、カテゴリごとの製品数をSQL Server側で集計させています。Accessにデータを全て持ってきてから集計するのではなく、SQL Serverに集計までやってもらう、まさにパススルー・クエリの醍醐味です。
4. `Set db = CurrentDb`:
- `CurrentDb` は、現在開いているAccessデータベース（.accdbや.mdbファイル）そのものを指し示す便利なオブジェクトです。
- `Set` キーワードを使って、`db` 変数に`CurrentDb`が返す`Database`オブジェクトへの参照を代入しています。オブジェクトの代入には必ず `Set` を使います。
5. `Set qdf = db.CreateQueryDef(“”, dbSQLPassThrough)`:
- いよいよパススルー・クエリの生成です！
- `db.CreateQueryDef` メソッドは、新しい `QueryDef` オブジェクトを作成します。
- 第1引数の `””`（空文字列）は、一時的なクエリを作成することを意味します。このクエリはAccessオブジェクトとして保存されず、VBAプロシージャの終了時に自動的に消滅します（もちろん、明示的に`Set qdf = Nothing`で解放することも重要です）。
- 第2引数の `dbSQLPassThrough` が、このクエリが「パススルー・クエリ」であることをAccessに教えています。これにより、AccessはこのクエリのSQLをそのままSQL Serverに渡すようになります。
6. `qdf.Connect = strConnect` と `qdf.SQL = strSQL`:
- 作成した `qdf` (QueryDefオブジェクト) のプロパティを設定します。
- `Connect` プロパティには、先ほど定義したSQL Serverへの接続文字列 (`strConnect`) を設定します。
- `SQL` プロパティには、SQL Serverで実行したいSQL文 (`strSQL`) を設定します。
7. `Set rs = qdf.OpenRecordset()`:
- `QueryDef` オブジェクトの `OpenRecordset` メソッドを呼び出すことで、定義したパススルー・クエリが実行されます。
- SQL Serverで実行された結果は、`Recordset` オブジェクトとして `rs` 変数に格納されます。この `Recordset` は、Accessのリンクテーブルでクエリを実行した結果と同じように、行と列を持つデータの集合として扱えます。
8. 結果の表示と `rs.MoveNext`:
- `If Not rs.EOF Then … End If` で、レコードセットにデータがあるかを確認します。`EOF` (End Of File) は、レコードの終端に達したことを示すプロパティです。
- `Do While Not rs.EOF … Loop` で、レコードセットの先頭から終端までをループ処理します。
- `Debug.Print rs!CategoryName & vbTab & rs!ProductCount`：レコードセットの各フィールドには、`rs![フィールド名]` の形式でアクセスできます。結果をイミディエイトウィンドウに表示しています。
- `rs.MoveNext`：これを忘れると無限ループに陥ります！ 次のレコードへ移動するための非常に重要なメソッドです。
9. オブジェクトの解放 (`Set rs = Nothing` など):
- ここが「極限の知見」と言える、最も重要なポイントの一つです！
- VBAでオブジェクト（`db`, `qdf`, `rs`など）を生成して使った後は、必ず `Set オブジェクト変数 = Nothing` として、そのオブジェクトが占有していたメモリやシステムリソースを解放しなければなりません。
- 特にデータベース接続に関わるDAOオブジェクトは、解放を怠ると以下のような問題を引き起こす可能性があります。
- メモリリーク: 不要なメモリが解放されず、Accessの動作が重くなったり、最終的にAccessがクラッシュしたりします。
- リソース枯渇: データベースへの接続数が限界に達し、他のユーザーが接続できなくなったり、新しい接続を確立できなくなったりします。
- パフォーマンス低下: 解放されないオブジェクトがバックグラウンドで処理を継続しようとして、システムの全体的なパフォーマンスを圧迫します。
- `rs.Close` でレコードセットを閉じるのも同様に重要です。
- これらは「お作法」ではなく、堅牢で安定したシステムを構築するための絶対的なルールです。これを徹底することで、あなたのAccessアプリケーションはプロの品質へと昇華します。
10. エラーハンドリング (`On Error GoTo ErrorHandler`):
- どんな完璧なコードでも、エラーは起こりえます。接続文字列の間違い、SQL構文エラー、ネットワーク障害など、様々な要因で実行時エラーが発生します。
- `On Error GoTo ErrorHandler` は、エラーが発生した際に指定したラベル（`ErrorHandler`）に処理をジャンプさせます。
- `MsgBox Err.Description` で、どのようなエラーが発生したかをユーザーに伝えます。
- `Resume Exit_Sub` で、エラー処理後に `Exit_Sub` ラベル（オブジェクト解放処理）にジャンプし、クリーンアップ処理を確実に実行します。これも非常に重要です。
5. よくある落とし穴と解決策
- 「ユーザー定義型は定義されていません」エラー:
- 原因: 「Microsoft DAO Object Library」または「Microsoft Office x.0 Access database engine Object Library」の参照設定ができていません。
- 解決策: 上記「準備」の項目を参考に、参照設定を確認・追加してください。
- 「ODBC — 接続に失敗しました。」エラー:
- 原因: `strConnect` の接続文字列が間違っています。サーバー名、データベース名、ユーザー名、パスワード、またはODBCドライバー名が正しくない可能性があります。
- 解決策: SQL Server Management Studioなどで接続情報を確認し、`strConnect` を正確に記述してください。特にドライバー名は、環境によって異なることがありますので注意が必要です。
- 「ODBC — SQLSTATE: 42000…」などのSQL Serverからのエラー:
- 原因: `strSQL` のSQL文に構文エラーがあります。Accessはパススルー・クエリのSQLをチェックしないため、SQL Serverがエラーを返しています。
- 解決策: `strSQL` の内容をコピーし、SQL Server Management Studioなどで直接実行してみて、エラーがないか確認してください。
- パフォーマンスが改善しない、Accessが不安定になる:
- 原因: オブジェクトの解放 (`Set obj = Nothing`) を忘れている可能性があります。
- 解決策: プロシージャの終了時に、`Dim` で宣言した全てのDAOオブジェクトに対して `Set obj = Nothing` を実行しているか確認してください。
6. 発展的な活用法
今回の基本をマスターすれば、パススルー・クエリの可能性は無限に広がります。
- パラメータクエリとしての利用:
- SQL文の中に `WHERE ProductID = ?` のようにパラメータを埋め込み、VBAから `qdf.Parameters(0).Value = 123` のように値を設定することで、動的な検索条件を持つパススルー・クエリを作成できます。
- データ更新クエリの実行:
- `INSERT`, `UPDATE`, `DELETE` 文を `strSQL` に設定し、`qdf.Execute dbFailOnError` のように `Execute` メソッドを使うことで、SQL Server上のデータを直接操作できます（この場合、`Recordset` は不要です）。
- ストアドプロシージャの実行:
- SQL文の代わりに `EXEC MyStoredProcedure @Param1 = 123` のようにストアドプロシージャを実行することも可能です。
- 結果のフォーム表示:
- 取得した `Recordset` を、フォーム上のサブフォームやリストボックスの `RecordSource` プロパティに設定することで、ユーザーインターフェースに結果を表示できます。
まとめ：Access VBAとパススルー・クエリで次のステージへ！
いかがでしたでしょうか？
今回は、Access VBAを使って `DAO.Database.CreateQueryDef` メソッドでパススルー・クエリを動的に生成し、SQL Serverの負荷を軽減しつつ高速なデータ処理を実現するテクニックを解説しました。
AccessがSQL Serverの力を最大限に引き出すためには、データ処理の「主役」をSQL Serverに任せる意識が大切です。そして、その制御をVBAで行うことで、あなたのAccessアプリケーションは、より高速に、より安定して、そしてより柔軟に動作するようになるでしょう。
オブジェクトのライフサイクルを意識し、適切な解放を行うことで、Access VBAのコードはただ動くだけでなく、堅牢で信頼性の高いものへと進化します。
今日学んだことを活かして、ぜひあなたのAccessアプリケーションを次のステージへと導いてください。応援しています！
