Accessで様々なデータを蓄積していくと、条件に応じてレコードを抽出する方法を知っておくと便利です。VBAでデータ処理をする際に予め条件に応じたレコードを抽出しておき、抽出したレコードでデータを処理すれば、処理速度を格段に向上させることもできます。
そこで、今回はAccess VBAとADOを活用し、条件に応じたレコードを抽出する方法を紹介します。
条件に応じたレコードを抽出するに際しては、データ型に応じて条件の記載が少し異なりますので、この点に注意しながら、本記事を参考にしていただければと思います。
サンプルデータについて
まずは、サンプルテーブルとして「T_従業員」テーブルを用意し、「氏名」、「年齢」、「入社年月日」及び「所属」フィールドを作成します。これらフィールドのデータ型は、それぞれ、「短いテキスト」、「数値型」、「日付/時刻型」及び「短いテキスト」とします。
作成したテーブルに、表1に示すようにサンプルデータを登録します。
表1 サンプルデータ
| 氏名 | 年齢 | 入社年月日 | 所属 |
| 幸田 翔平 | 48 | 1992/11/14 | 営業課 |
| 折原 美姫 | 33 | 2015/12/21 | 技術一課 |
| 下村 辰雄 | 32 | 2018/07/01 | 技術二課 |
| 東野 藤子 | 61 | 1984/05/12 | 経理課 |
| 平野 遥佳 | 37 | 2010/12/10 | 技術一課 |
| 樋口 啓司 | 28 | 2019/08/29 | 営業課 |
| 中塚 敏夫 | 58 | 1995/11/17 | 人事課 |
続いて、Filterプロパティについて確認していきましょう。
Filterプロパティについて
Filterプロパティは、次のような記載をします。
Object.Filter = expression
ここで、ObjectにはForm、Report等も入りますが、今回はADOを用いますのでレコードセットが入ります。
「expression」には 、WHERE句(“名前 = ‘太郎'” など)のように記載します。 複合句を作成するには、個々の句を「AND」または「OR」で連結します。検索条件文字列を指定する際は、次の点に注意してください。
一つ目に、フィールド名はADOで参照するテーブルのレコードセットで有効なフィールド名を指定する必要があります。フィールド名にスペースを含める場合は、名前を角かっこで囲みます。
次に、演算子は「=」、「>」、「<」、「>=」、「<=」や「LIKE」を指定する必要があります。
条件に用いる値は、文字列に対してはシングルクオーテーション「’」、日付/時刻型にはシャープ「#」で括る必要があります。演算子が「LIKE」の場合、値はワイルドカードを使用します。ワイルドカードはアスタリスク 「*」 とパーセント記号「%」 のみを使用できます。
最後に、Filterプロパティを長さ 0 の文字列 「””」 に設定すると、「adFilterNone」定数を使った場合と同じ結果が得られます。
以上を踏まえて、次からVBAによる実装をしていきます。
サンプルコード
今回実装するコードは、以下のとおりとします。
① 所属が「営業課」の者を抽出する。
② 所属が「技術」の者をあいまい抽出する。
③ 年齢が30歳以上の者を抽出する。
④ 入社年月日が2010年から2015年までの者を抽出する。
早速、以下のコードを標準モジュールに実装してみましょう。なお、ADOを利用しますので参照設定を忘れないようにしてください。標準モジュールの作成や参照設定の方法は以下の記事をご参考にしてください。
Private Sub UseOfFilter()
'ADOを用いるための変数宣言
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'データベース接続
Set cn = CurrentProject.Connection
'「T_従業員」テーブルを開く
rs.Open "T_従業員", cn, adOpenKeyset, adLockOptimistic
'① 所属が「営業課」の者を抽出する。
rs.Filter = "所属 = '営業課'"
'ループ文で抽出したレコードをイミディエイトウィンドウに表示する。
Debug.Print "【営業課】"
Do Until rs.EOF = True Or rs.BOF = True
Debug.Print "氏名： " & rs!氏名
rs.MoveNext
Loop
'② 所属が「技術」の者をあいまい抽出する。
rs.Filter = "所属 Like '技術*'"
'ループ文で抽出したレコードをイミディエイトウィンドウに表示する。
Debug.Print "【技術】"
Do Until rs.EOF = True Or rs.BOF = True
Debug.Print "氏名： " & rs!氏名
rs.MoveNext
Loop
'③ 年齢が30歳以上の者を抽出する。
rs.Filter = "年齢 >= 30"
'ループ文で抽出したレコードをイミディエイトウィンドウに表示する。
Debug.Print "【30歳以上】"
Do Until rs.EOF = True Or rs.BOF = True
Debug.Print "氏名： " & rs!氏名
rs.MoveNext
Loop
'④ 入社年月日が2010年から2015年までの者を抽出する。
rs.Filter = "入社年月日 >= #2010/01/01# AND 入社年月日 <= #2015/12/31#"
'ループ文で抽出したレコードをイミディエイトウィンドウに表示する。
Debug.Print "【2010年～2015年入社】"
Do Until rs.EOF = True Or rs.BOF = True
Debug.Print "氏名： " & rs!氏名
rs.MoveNext
Loop
'データベースを閉じる。
rs.Close
cn.Close: Set cn = Nothing
End Sub
サンプルコードで何をしているか確認していきましょう。
まずは、ADOを用いるための変数宣言をしています。そして、カレントデータベースに接続をし、「T_従業員」テーブルを開く処理となります。
そして、お待ちかねの「Filterプロパティ」を用いています。「rs」はレコードセットを意味していますので、ここでは「rs」レコードセットに対してFilterプロパティにより条件に応じたレコード抽出を行います。①については、「所属」が「営業課」となるレコードを抽出する条件を記載しています。
「Debug.Print」を用いてイミディエイトウィンドウに値を表示させますが、Do Loop文で複数抽出されたレコードセットの繰り返し処理をしています。レコードセットがEOFあるいはBOFになった段階で繰り返し処理を終了します。
この際注意すべき点として、検索する値が「文字列」であるため対象をシングルクオーテーションで括っています。
②は、文字列のあいまい検索です。「LIKE」演算子を用いますが、ワイルドカードとしてアスタリスクを用いており、「所属」フィールドで「技術」を含むレコードを全て抽出する処理となります。その後の処理は先と同様です。
③は、数値に対する検索です。演算子に不等号を用いて「30歳以上」のレコードを抽出する条件を設定しています。また、数値型に対しては文字列と異なりシングルクオーテーションで括ることはしない点にも注意してください。
④は、日付に対する検索です。演算子には不等号を用い、「AND」で二つの条件による複合句を作成しています。また、「日付/時刻型」であるため対象をシャープで括っていることもポイントです。
最後に、開いていたテーブルを閉じ、データベース接続を切って終了です。
サンプルコードを実行すると、図1の結果が得られます。
図1に示すようにFilterプロパティにより必要情報を上手く抽出できていることが分かります。
まとめ
今回は、ADOにおけるFilterプロパティを用いて、必要な情報を効率良く抽出し、処理をする方法を紹介しました。Filterプロパティは様々な場面で活用することができ、例えば、フォームに検索機能を設ける方法を以下の記事で紹介していますので、ご参考になさってください。
また、専門書でも、VBAコードの書き方が詳しく説明されていますので、お手元に1冊用意されてAccess開発に臨まれることをオススメします。
スポンサーリンク
コメント