Access VBAでシステム開発を続けていると、
- SQL文が長くなって管理できない
- 同じSQLを何度も書いている
- パラメータ付き検索をしたい
- SQLの修正箇所が分散している
- 保守性を高めたい
といった課題が発生します。
Access VBAでSQLを多用するならQueryDef（クエリ定義）を活用することで保守性と開発効率を大きく向上できます。
初心者はDAOのRecordsetやExecuteを覚えた後、次のステップとしてQueryDefを学ぶことをおすすめします。
実際の業務システムでは、
顧客検索
売上集計
設備履歴抽出
CSV取込チェック
帳票データ作成
などで頻繁に利用されています。
- QueryDefとは
- QueryDefのメリット
- 基本コード
- 逆引き① 保存クエリを実行したい
- 逆引き② QueryDefからRecordset取得
- 逆引き③ パラメータクエリを実行したい
- 逆引き④ SQLを書き換えたい
- 逆引き⑤ 更新クエリを実行したい
- 実務活用例① 顧客検索システム
- 実務活用例② 売上管理システム
- 実務活用例③ CSV取込システム
- 実務活用例④ 設備管理システム
- 実務活用例⑤ 帳票システム
- QueryDefとSQL文字列の違い
- QueryDefとExecuteの関係
- ベテラン開発者の定番構成
- Copilot活用術① QueryDef生成
- Copilot活用術② SQLレビュー
- Copilot活用術③ QueryDef設計
- QueryDef利用時の注意点
- QueryDefが向いている処理
- 開発現場での利用頻度
- まとめ
QueryDefとは
QueryDefとは、
クエリ定義オブジェクト
です。
Access内に保存されているクエリをVBAから操作できます。
イメージ
Accessクエリ
↓
QueryDef
↓
VBAで利用
QueryDefのメリット
通常のSQLです。
strSQL = _
"SELECT * " & _
"FROM T_CUSTOMER " & _
"WHERE 顧客区分='A'"QueryDefなら
Q_CUSTOMER_SEARCH
というクエリを作成し、
CurrentDb.QueryDefs("Q_CUSTOMER_SEARCH")で利用できます。
メリット
SQL管理が楽
可読性向上
保守しやすい
再利用しやすい
Copilotでレビューしやすい
基本コード
QueryDef取得
Dim qdf As DAO.QueryDef
Set qdf = _
CurrentDb.QueryDefs( _
"Q_CUSTOMER")逆引き① 保存クエリを実行したい
最も基本です。
CurrentDb.QueryDefs( _
"Q_UPDATE") _
.Execute例
売上確定処理
一括更新処理
逆引き② QueryDefからRecordset取得
検索処理です。
Dim rs As DAO.Recordset
Set rs = _
CurrentDb.QueryDefs( _
"Q_CUSTOMER") _
.OpenRecordset利用
Do Until rs.EOF
Debug.Print _
rs!顧客名
rs.MoveNext
Loop逆引き③ パラメータクエリを実行したい
非常に実務的です。
Accessクエリ
SELECT *
FROM T_CUSTOMER
WHERE 顧客コード=[P_CODE]VBA
Dim qdf As DAO.QueryDef
Set qdf = _
CurrentDb.QueryDefs( _
"Q_CUSTOMER")
qdf.Parameters( _
"P_CODE") = "C001"Recordset取得
Set rs = _
qdf.OpenRecordset逆引き④ SQLを書き換えたい
動的SQLです。
Dim qdf As DAO.QueryDef
Set qdf = _
CurrentDb.CreateQueryDef("")SQL設定
qdf.SQL = _
"SELECT * " & _
"FROM T_CUSTOMER"実行
Set rs = _
qdf.OpenRecordset逆引き⑤ 更新クエリを実行したい
売上更新です。
CurrentDb.QueryDefs( _
"Q_UPDATE_SALES") _
.Execute効果
SQLをVBAから切り離せる
実務活用例① 顧客検索システム
検索画面です。
QueryDef
SELECT *
FROM T_CUSTOMER
WHERE 顧客コード=[P_CODE]VBA
qdf.Parameters("P_CODE") = _
Me!txtCodeメリット
検索ロジックを共通化できます。
実務活用例② 売上管理システム
売上集計です。
クエリ
月別売上集計
VBA
Set rs = _
CurrentDb.QueryDefs( _
"Q_MONTHLY_SALES") _
.OpenRecordset集計ロジックをVBAに書かずに済みます。
実務活用例③ CSV取込システム
重複チェックです。
クエリ
Q_CHECK_DUPLICATE
VBA
Set rs = _
CurrentDb.QueryDefs( _
"Q_CHECK_DUPLICATE") _
.OpenRecordset保守性が向上します。
実務活用例④ 設備管理システム
設備履歴検索です。
パラメータ
設備番号
QueryDef
qdf.Parameters( _
"P_MACHINE") = _
strMachine検索仕様変更にも強くなります。
実務活用例⑤ 帳票システム
レポート出力です。
クエリ利用
請求書
納品書
見積書
効果
レポートと連携しやすくなります。
QueryDefとSQL文字列の違い
SQL文字列
strSQL = …
メリット
簡単
柔軟
デメリット
保守が難しい
QueryDef
Q_CUSTOMER
メリット
保守しやすい
再利用しやすい
設計がきれい
実務ではQueryDefが好まれます。
QueryDefとExecuteの関係
例えば
CurrentDb.Execute strSQLの代わりに
CurrentDb.QueryDefs( _
"Q_UPDATE") _
.Executeも可能です。
利点
SQL修正をVBA側へ持ち込まない
ことです。
ベテラン開発者の定番構成
実務では次の構成が多いです。
Q_CUSTOMER_SEARCH
Q_SALES_SUMMARY
Q_IMPORT_CHECK
Q_LOG_OUTPUT
VBAでは
CurrentDb.QueryDefs(...)だけ呼び出します。
効果
VBA
↓
処理制御専用
SQL
↓
クエリ管理
になります。
Copilot活用術① QueryDef生成
Copilotへの依頼例です。
Accessクエリです。
顧客検索用の
パラメータクエリを
作成してください。
Copilot活用術② SQLレビュー
以下のクエリを
パフォーマンス面で
レビューしてください。
Copilot活用術③ QueryDef設計
売上管理システムです。
QueryDef構成案を
作成してください。
QueryDef利用時の注意点
名前変更
Q_CUSTOMER
を変更するとVBA側も修正が必要です。
パラメータ名
qdf.Parameters(“P_CODE”)
は一致させます。
不要クエリ整理
使わないクエリを残さないようにしましょう。
QueryDefが向いている処理
- 顧客検索
- 売上集計
- CSV取込チェック
- レポート出力
- 帳票作成
- 設備管理
- 履歴検索
開発現場での利用頻度
| 順位 | 用途 |
|---|---|
| 1位 | 検索クエリ |
| 2位 | 集計クエリ |
| 3位 | 帳票用クエリ |
| 4位 | CSV取込チェック |
| 5位 | 更新クエリ |
中規模以上のAccessシステムでは非常によく利用されます。
まとめ
QueryDefはAccess VBAにおけるSQL管理の強力な仕組みです。
特に
- 顧客検索
- 売上集計
- レポート出力
- CSV取込
- 設備管理
などで大きな効果を発揮します。
DAOの学習順としては、
OpenRecordset
↓
Execute
↓
Transaction
↓
QueryDef
がおすすめです。
QueryDefを習得すると、
SQL管理がきれいになる
保守しやすい
Copilotレビューしやすい
という大きなメリットがあります。
Access VBAで長期運用される業務システムを開発するなら、ぜひ活用しておきたい技術です。
コメント