連載
» 2015年09月11日 05時00分 UPDATE

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

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

[薬師寺国安,PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本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のように配置する。コントロールを一つ配置すると「ウイザード」が表示されるが、全て「キャンセル」を選択する。

AXS_EXL3_01.png 図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のようになる。

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

QueryDefを使いこなそう

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

著者紹介

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

kuniyasu.jpg

薬師寺国安事務所代表。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)。


Copyright© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。