Access VBA ADOによるレコード操作

VBA

Accessでレコードの操作をする際に、ある特定のフィールド情報を取得して出力する等、細かなデータ処理が必要な場合に、クエリによるデータ操作では限界があります。そこで、ADOと呼ばれるデータアクセス技術を利用することをお勧めします。

ADOはActiveX Data Objectsの略称で、Microsoft社が提供するデータベースアクセス技術です。ADOの詳細については、別に数多くの説明がされていますので、ここで改めて説明をすることは割愛しますが、まずは、「習うより慣れる」の精神で、ADOを使えるように設定をしていきましょう。

スポンサーリンク

サンプルテーブルについて

今回は、サンプルテーブルを1つ用意し、レコードの情報をイミディエイトウインドウ上で確認する方法を見てみましょう。

まず、サンプルテーブルの構造は表1に示すとおりです。

表1 サンプルテーブルの構造

フィールド名データ型
IDオートナンバー型
氏名短いテキスト
年齢数値型

次に、サンプルデータは表2のようにしました。

表2 サンプルデータについて

ID氏名年齢
1鈴木太郎24
2佐藤次郎39
3高橋花子42

ADOを使うための設定

VBA画面(ショートカットキーは 「Alt + F11」 です。)を起動し、ツールタブの参照設定を開きます。

図1 参照設定の開き方

次に、「Microsoft ActiveX Data Objects 6.1 Library」にチェックを入れます。

図2  Microsoft ActiveX Data Objects 6.1 Library の設定

以上の操作で、ADOを使えるようになりました。

ADOによるデータ取得方法

ADOによるデータ取得に際しては、データベースへの接続をする必要があります。今回は、自身のデータベースアクセスになります。VBA画面で標準モジュール・プロシージャを作成(過去記事をご参照ください。)し、以下のソースコードを記入します。

Public Sub Data_get()
 Dim cn As New ADODB.Connection  'データベース接続用変数の設定
 Dim rs As New ADODB.Recordset   'レコードセット用変数

 '自データベースの接続設定
 Set cn = CurrentProject.Connection

 'サンプルテーブルを開く
 rs.Open "サンプルテーブル", cn, adOpenKeyset, adLockOptimistic

 '繰り替えし処理で、各レコードの値をイミディエイトウインドウに表示する。
 Do Until rs.EOF = True

  'rs!フィールド名でサンプルテーブルのフィールドを指定
  Debug.Print rs!ID & ", " & rs!氏名 & ", " & rs!年齢
  
  'レコードセットの移動
  rs.MoveNext
 
Loop

 'テーブルを閉じる
 rs.Close

 'データベースの接続を解除する。
 cn.Close: Set cn = Nothing
 End Sub

まず、cnとrs変数を設定していますが、これらがADOによるデータベース接続用変数となります。一種のおまじないのようなものだと思ってください。

次に「Set cn = CurrentProject.Connection」で自データベースの接続をしています。他のAccessファイルを開く場合には、

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\TEST.mdb"

のように記載します。

「rs.Open “サンプルテーブル”, cn, adOpenKeyset, adLockOptimistic」で「サンプルテーブル」を開くように指定していますが、テーブルの他にも、クエリやSQL文を指定することも可能です。

例えば、次のようにSQL文を指定することもできます。

Dim mySQL as String
mySQL = "SELECT * FROM サンプルテーブル WHERE 年齢 >= 30;"

rs.Open mySQL, cn, adOpenKeyset, adLockOptimistic

次に、「adOpenKeyset」はCursorTypes引数の一つです。この引数部分では、レコードを参照するカーソルの種類を指定しますが、話が難しくなりますので、「adOpenKeyset」を指定しておけば、一通りの操作で困ることはありません。

さらに、「adLockOptimistic」はLockType引数の一つです。この引数部分ではレコードセットを開く際のロック方法を指定しますが、やはり話が難しくなりますので、「adLockOptimistic」を指定しておけば、一通りの操作で困ることはありません。

Do loop文では、「rs.EOF = True」と指定しましたが、これはレコードセットが「End Of File」、すなわちレコードの終端(これ以上読み込めるレコードがない状態)になるまで繰り返し操作を行うという意味です。ネストの中で、「rs.MoveNext」としていますが、これによりレコードセットの移動をしているため最終的にはレコードの終端に到達することになります。

Debug.Print ~」は「~」の内容をイミディエイトウインドウに表示することを意味しており、「rs!フィールド名」が当該テーブル中の該当フィールドを指定することになります。繰り返し処理でサンプルテーブルのレコード情報をすべて表示した後、rs.EOF =True となり、Loopから抜け出します。
なお、「rs」変数の後ろにフィールド名を繋げる場合は、エクスクラメーションマークを用いますが、プロパティ等の場合にはピリオドを使うため(rs.EOFのように)、混同しないようにご注意ください。

最後に、「rs.Close」でアクセスしていた「サンプルテーブル」を閉じ、さらに「cn.Close」でデータベースへの接続も解除することになります。また、「Set cn = Nothing」は一種のガーベージコレクションとなります。

以上がソースコードの説明になり、実際に処理を実行してみると、図3に示す結果が得られると思います。

図3 イミディエイトウインドウへの出力結果

なお、「サンプルテーブル」の代わりに、上述で示したSQL文に対して処理を実行すると、WHERE条件として30歳以上のレコードセットが抽出され、年齢が24歳である鈴木太郎のレコードセットは抽出されませんので、併せて確認してみてください。

まとめ

今回は、データベースの細かなデータ操作方法としてADOについて紹介をしました。上で紹介した方法は非常に単純な内容となりますので、次回はフォーム上の操作を含めてADOの活用方法を紹介したいと思います。

また、以下に紹介している書籍では、VBAコードの書き方が詳しく説明されていますので、お手元に1冊用意されてAccess開発に臨まれることをオススメします。

スポンサーリンク

コメント

タイトルとURLをコピーしました