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

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

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

サンプルテーブルについて
ADOを使うための設定
ADOによるデータ取得方法
まとめ

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

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

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

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

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

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

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

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

ADOを使うための設定

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

次に、「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に示す結果が得られると思います。