Access DAOを用いて選択クエリを作成する方法

Accessでデータ処理をする際に、恒久的にクエリを作成すると保守性が損なわれるため、クエリを一時的に作成して処理をすることがあります。

この際に、DAOを用いて選択クエリを作成する方法が比較的容易に実装できますので、紹介していきます。

サンプルテーブル
SQL文について
VBAでの実装
VBAによる実行
まとめ

サンプルテーブル

選択クエリを作成するための基となるテーブルとして「T_選択クエリ作成」テーブルを作成します。

このテーブルには「氏名」、「生年月日」及び「性別」のフィールドを設定し、それぞれ「短いテキスト」、「日付/時刻型」及び「短いテキスト」のデータ型を設定します。

次に、表１に示すようにサンプルデータを登録します。

表1　サンプルデータ

氏名	生年月日	性別
坂本　花子	1998/01/06	女
山本　寛治	1978/04/05	男
森本　真一	1997/12/07	男
相川　ふみ	2001/06/19	女

以上でサンプルデータの準備ができましたので、次から選択クエリについて、確認していきましょう。

SQL文について

DAOで選択クエリを作成するために、SQL文を記載する必要がありますので改めてその基本構文を確認します。

SELECT フィールド名 FROM テーブル名

これが基本的な選択クエリをSQL文で記載する方法で、複数フィールドを表示させたい場合は、「SELECT」と「FROM」の間にカンマ区切りでフィールド名を列記します。

また、条件抽出したい場合は、「FROM」の後ろに「WHERE ～」と条件を記載します。

今回の例で「男性のレコード」だけを抽出したい場合のWHERE句は、「WHERE 性別 = ‘男’」と記載します。WHERE句を上手く用いることで、可変的な選択クエリを作成することもできます。

例えば、「F_Main」フォーム上に日付/時刻型のデータを入力する「t_test」テキストボックスがあったとします。

この際、「WHERE 生年月日 >= # & Forms!F_Main!t_test & #」として、「F_Main」フォーム上の「t_test」テキストボックスの日付を取得することで、この日より生年月日が先のレコードを抽出する選択クエリを作成することも可能です。

選択クエリについては、以下の記事でも紹介していますので、ご参考になさってください。

VBAでの実装

SQLの記載方法を確認した上で、VBA DAOを用いて選択クエリを作成する方法を見ていきましょう。ここで、DAOによる選択クエリ作成の構文は以下のように記載できます。

CreateQueryDef（Name, SQL Text）

引数の「Name」は新しいQueryDefの一意の名前を表す値で、主だっては作成する選択クエリの名称を記載します。「SQL Text」はQueryDefを定義するSQL文を記載します。

また、既に同じ名称のクエリが存在しているとエラーが返されますので、予め同一名称のクエリがないかをチェックし、仮に同じ名称のクエリがあった場合は、これを削除する機能を設けます。

以上のことを踏まえて、作成する選択クエリの名称は「Q_男性抽出」とし、VBA画面で以下コードを入力してください。

Private Sub CreateQuery()

'変数を宣言する。
Dim db As DAO.Database
Dim mySQL As String
Dim qrDef As DAO.queryDef
Dim QueryName As String

'作成する選択クエリ名称
QueryName = "Q_男性抽出"

'カレントデータベースへの接続
Set db = CurrentDb

'選択クエリのSQL文
mySQL = "SELECT 氏名, 生年月日 FROM T_選択クエリ作成 WHERE 性別 = '男'"

'すでに同一クエリが存在している場合は削除する。
For Each qrDef In db.QueryDefs
　　If qrDef.Name = QueryName Then
　　
　　　　'クエリの削除
　　　　db.QueryDefs.Delete QueryName 
　　
　　End If
Next

'クエリの作成
db.CreateQueryDef QueryName, mySQL

'データベース接続の解除
db.Close

'選択クエリを作成したことのメッセージを表示する。
MsgBox QueryName & "を作成しました。"

End Sub

それでは、各コードを確認していきましょう。まずは、お約束の変数を宣言しています。

続いて、作成する選択クエリの名称を変数に格納します。特に、クエリ名称が複数箇所で使われる場合、最初に変数へ格納しておくことで、その後の名称変更等も容易に修正が可能となります。

次に、データベースへの接続をしますがDAOでは決まった表現となりますので、この機会に覚えてしまいましょう。そして、作成する選択クエリのSQL文を変数へ格納します。

このまま、CreateQueryDefを用いてクエリを作成することもできますが、既に同一クエリが存在しているとエラーとなりますので、QueryDefsの中に同じものがないかをFor文を用いて繰り返し確認します。この時、同一名称のクエリが存在した場合は、削除する処理を記載しています。

そして、上述のとおりCreateQueryDefでクエリを作成し、最後にデータベース接続を解除します。これら処理は一瞬で終了しますので、ユーザーが処理を完了したことが気付けるようにメッセージボックスで終了の旨を表示するようにしています。

以上の処理をVBA画面に追加したものが図１となります。

VBAによる実行

早速、実装したコードを動かしてみましょう。VBA画面で先ほど作成した「CreateQuery」Subにカーソルを合わせてVBA画面上の「▶」アイコンをクリックすることで処理が開始されます。

処理が完了すると、「Q_男性抽出を作成しました。」というメッセージボックスが表示されますので、そのまま「OK」をクリックします。

これにより、ナビゲーションウインドウに「Q_男性抽出」が作成されていますので、実際に開いてみると図２のような結果が得られます。

このようにして、SQL文を上手く組み合わせることで選択クエリやその他の種類のクエリを作成することができます。

まとめ

今回は、DAOを用いた選択クエリの作成方法を紹介しました。例えば、WHERE条件が可変的な場合、毎回選択クエリを作成することは効率が悪く、保守性の観点からも好ましくありません。このため、今回紹介したように選択クエリ自体を可変的に作成することで、効率良くデータ処理ができ、また保守性も高めることができますので、是非ご参考にしていただければと思います。

ここで紹介した内容は、専門書などでも紹介されていますので、ご参考になさってください。