Access VBAでSQLを実行するQueryDefオブジェクト、CreateQueryDefメソッド、QueryDefsコレクションの使い方の基本：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、クエリを定義する場合に使用する「QueryDef」オブジェクトやデータベースにクエリを作成する「CreateQueryDef」メソッドの使い方などについて。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013＋Access 2013

　今回は、Accessのフォームからデータを入力し、入力したデータを基にSQLで検索を行い、抽出した結果をExcelのシートに書き出す処理を解説する。

　使用するテーブルは、TIPS「AccessからExcelに接続して開く方法＆Accessで作成したデータをExcelに出力する方法で学ぶ、連携VBAの基本」で作成しておいた、「商品管理テーブル」だ。「商品管理データベース.accdb」をクリックして、Accessを起動しておいてほしい。

Accessのフォームとコントロールの配置

　Accessメニューの「作成」→「フォームデザイン」と選択して、「フォームデザイン」の画面を表示する。

　「フォーム1」タブの上でマウスの右クリックをして、表示されるメニューから「上書き保存」を選択し、「フォーム名」を「SQLで抽出したデータをExcelに書き出すフォーム」という名前にしておく。すると、フォームの「デザイン画面」のタブが「フォーム1」から「SQLで抽出したデータをExcelに書き出すフォーム」という名前に変化する。

　次に、「SQLで抽出したデータをExcelに書き出すフォーム」画面に、表1のコントロールを配置していく。

表1　配置するコントロールと「名前」

コントロール名	名前
テキストボックス	商品名テキストボックス
ボタン	実行ボタン

　表1のコントロールを図1のように配置する。コントロールを一つ配置すると「ウイザード」が表示されるが、全て「キャンセル」を選択する。

図1　各コントロールを配置した結果

　各ラベルやテキストボックスの書式は「プロパティシート」から設定する。ラベルに指定している項目名は、「書式」タブの「標題」から指定する。後は、「フォント名」「フォントサイズ」「フォント太さ」を読者の好みに応じて設定してほしい。テキストボックスや「ボタン」に付ける「名前」は「その他」タブの「名前」から指定する。

VBEを起動してコードを記述する

　配置した「実行ボタン」を選択した状態で、プロパティシートの「イベント」タブ→「クリック時」の右横にある「v」アイコン→「イベント プロシージャ」を選択する。次に、「…」アイコンをクリックすると、VBE（Visual Basic Editor）が起動して、「実行ボタン_Click()」のプロシージャのひな型が作成されている。この中にリスト1のコードを記述する。

Private Sub 実行ボタン_Click()
  Dim SQL As String
  Dim FileName As String
  FileName = "C:\Excel2013Data\商品名で抽出.xls"
  Dim db As Database
  Set db = CurrentDb
  Dim myQuery As QueryDef
  
  Set myQuery = db.CreateQueryDef("商品名データ", "Select * from 商品管理テーブル")
  SQL = "Select * FROM 商品管理テーブル Where 商品名 LIKE " & "'*" & 商品名テキストボックス.Value & "*';"
  db.QueryDefs![商品名データ].SQL = SQL
  
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "商品名データ", FileName, True, "商品名で抽出"
  
  Application.CurrentDb.QueryDefs.Delete "商品名データ"
  
  db.Close
  Set db = Nothing
  MsgBox (FileName & "を書き出しました。")
End Sub

リスト1　 AccessのフォームからExcelのシートにデータを表示するコード

「実行ボタン」がクリックされたときの処理

　まずは、初期設定だ。文字列型の変数、SQLとFileNameを宣言する（2〜3行目）。変数FileNameに完全パス付ファイル名を格納する。今回はファイル名を「商品名で抽出.xls」としている（4行目）。「C:\Excel2013Data」フォルダーは事前に作成しておいてほしい。

カレントデータベースオブジェクト「CurrentDb」

　Database型の変数dbを宣言し、カレントデータベースオブジェクト「CurrentDb」を参照する変数dbにセットする（5〜6行目）。

クエリを定義する場合に使用する「QueryDef」オブジェクト

　7行目で、QueryDef型の変数myQueryを宣言する。QueryDefは、クエリを定義する場合に使用するオブジェクトだ。

データベースにクエリを作成する「CreateQueryDef」メソッド

　CreateQueryDefメソッドで、データベースにクエリを作成し、変数myQueryにセットする（9行目）。書式は下記の通りだ。

CreateQueryDefメソッドの書式

Set 変数=Database.CreateQueryDef(Name,SQLText)

　「Name」は省略可能で、作成するクエリ名を指定する。「SQLText」は省略可能で、SQL文を使ってクエリの内容を記述する。

　今回は、「Name」に「商品名データ」を指定し、「SQLText」に「商品管理テーブル」の全レコードを抽出する「Select * from 商品管理テーブル」というSQL文を指定している。

クエリオブジェクトのコレクション「QueryDefs」と「!」

　変数SQLには、「商品名」が「商品テキストボックス」に入力した値を含む全レコードを「商品管理テーブル」から抽出するクエリを格納する。LIKE演算子を使って「あいまい検索」としている（10行目）。

　11行目では、10行目の変数SQLを使って、「商品名」が「商品名テキストボックス」に入力された値を含むデータを抽出している。

　「db.QueryDefs![商品名データ]」で使用している「!」（感嘆符）は、「商品名データ」がクエリオブジェクトのコレクション「QueryDef」の構成要素であることを表している。コレクション要素であることを明示する場合に「!」を使用するのだ。

AccessのデータをExcelに出力する

　13行目では、DoCmd.TransferSpreadsheetメソッドで、AccessのデータをExcelに出力する。詳細は、TIPS「AccessからExcelに接続して開く方法＆Accessで作成したデータをExcelに出力する方法で学ぶ、連携VBAの基本」を参照してほしい。

QueryDefs.Deleteメソッドでクエリを削除しておく

　15行目で、必要なデータが抽出された後、「商品名データ」クエリを削除しておく。

　この処理をしていないと、再度「商品名テキストボックス」に抽出したいデータを入力して「実行」ボタンをクリックした場合、「すでに‘商品名データ’クエリが存在する」のメッセージが出てエラーになる。そこで、一度作成した「クエリ（商品名データ）」を削除しておく必要があるのだ。

CurrentDb.Closeメソッドでクローズ処理

　17〜19行目で、データベースを閉じ、全ての関連付けから解放し、最後に抽出したデータを保存した旨のメッセージを表示する。

実行結果

　今回のサンプルは、抽出したデータをExcelシートに保存するだけで、続けて抽出したい「商品名」を入力しても、追加されて保存されることはないので注意してほしい。最後に検索した「商品名」のデータがExcelシートに保存されるだけだ。

　実行すると、図2のようになる。

図2　Accessのフォームから抽出したい「商品名」を入力して、Excelのシートに保存した

QueryDefを使いこなそう

　今回は、これで終わりだ。クエリを作成して必要なデータを抽出するには、QueryDefオブジェクトやCreateQueryDefメソッド、QueryDefsコレクションを使う必要がある。これらの使い方を理解できれば、Access VBAを使うさまざまな場面での応用が可能になるので、ぜひマスターして使ってみていただきたい。

著者紹介

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms - Windows Platform Development (Oct 2014-Sep 2015）。