VBAとデータベース レコードセットについて

さて、レコードセットとはなんでしょうか。これについてはすでになんどか説明しましたが、データベースのデータ全体やクエリの実行で取得したデータのことをレコードセットといいます。具体的に使う方法はこっちの記事やこっちの記事で既に説明しましたが、この記事ではより詳しく説明してみたいと思います。

Sub Test()
    Dim adoCON      As New ADODB.Connection
    Dim adoRS       As New ADODB.Recordset
    Dim strSQL      As String
    Dim odbdDB      As Variant
    Dim wSheetName  As Variant
    Dim i           As Integer
   
    odbdDB = ActiveWorkbook.Path & "\DB_name.accdb"
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                        & "Data Source=" & odbdDB & ""
    adoCON.Open
    adoRS.CursorLocation = adUseClient
    strSQL = "SELECT table_name.* FROM table_name ORDER BY table_name.ID;"
    adoRS.Open strSQL, adoCON, adOpenDynamic
    i = 1
    Do Until adoRS.EOF
        With Worksheets("Sheet1")
            .Cells(i, 1).Value = adoRS!ID
            .Cells(i, 2).Value = adoRS!Name
            .Cells(i, 3).Value = adoRS!age
        End With
        i = i + 1
        adoRS.MoveNext
    Loop　
    adoRS.Close
    Set adoRS = Nothing
    adoCON.Close
    Set adoCON = Nothing
End Sub

こういうコードがあったとします。
レコードセットを扱うには、まずはレコードセットオブジェクトをセットしなければなにも始まりません。こいつをまずセットします。

 Dim adoRS       As New ADODB.Recordset

変数宣言と同じ場所にこう書きます。

ついでにコネクションオブジェクトもセットしておきます。こいつがないとデータベースにアクセスできません。

 Dim adoCON      As New ADODB.Connection
 Dim adoCON      As New ADODB.Connection

名前は何でも構いません。johnCONでもいいし、connectADOでもいいし、tanakaConnectでも構いません。読む人が分かるように名前をつけてあげます。

次にコネクションオブジェクトを使ってデータベースに接続します。

odbdDB = ActiveWorkbook.Path & "\DB_name.accdb"

adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                        & "Data Source=" & odbdDB & ""
adoCON.Open

ここまでエラーなくできればデータベースへの接続が成功しています。


さらにレコードセットオブジェクトのCursorLocationメソッドを使ってサーバー側とクライアント側のどちらでデータ管理をするのか決めます。

adoRS.CursorLocation = adUseClient

このようにadUseClientを宣言すればクライアント側でデータ管理することになります。基本的にはOpenメソッドでレコードセットを開く前に設定します。Openメソッド実行後は、読み取り専用プロパティとなります。adUseServerと書けばサーバー側でデータ管理することになります。

カーソル（Cursor）の管理をサーバ側で行うと共有や排他に強く、クライアント側で行うと検索や絞り込みに強いという特徴があります。

strSQL = "SELECT table_name.* FROM table_name ORDER BY table_name.ID;"

データの絞りこみに使うSQL文は長くなるのでstrSQLというString型の変数に入れておきます。SQL文については後で詳しく解説します。

ここでようやくレコードセットが使えるようになります。
レコードセットは次のようにOpenメソッドを使って開きます。

Recordset.Open([Source], [ActiveConnection], [CursorType], [LockType], [Options])

私はこのように開きました。

adoRS.Open strSQL, adoCON, adOpenDynamic

[Source]には読み込み対象のテーブル名、選択クエリ名、SQL（SELECT文など）を記述します。例として書いたプログラムではstrSQLを入れています。

[ActiveConnection]にはデータソースへの接続が完了しているコネクション（Connectionオブジェクト）を指定します。例として書いたプログラムではadoCONを入れています

[CursorType]にはテーブルやクエリを開くときに使用するカーソルの種類を指定します。例のプログラムではadOpenDynamicを指定しています。他のカーソルの名称やそれぞれのカーソルの働きについては以下の表をご覧ください。

表の引用元：Access VBA Tips+α 「カーソルの場所を設定する」表1、2015年10月25日閲覧。

[LockType]には排他制御の方法を指定します。僕は使ったことがないのでよくわかりませんが、同時に色々な検索やテーブルのデータの更新なんかをする際に指定するのかも？

LockTypeの名称や働きの詳細については以下の表をご覧ください。

表の引用元：Access VBA Tips+α 「テーブルやクエリを開く」表2、2015年10月25日閲覧。

[Options]にはSource 引数が Command オブジェクト以外のソースを表す場合の解釈方法を指定するようですが、使ったことがないのでわかりません。詳細は以下をご覧ください。

表の引用元：Access VBA Tips+α 「テーブルやクエリを開く」表3、2015年10月25日閲覧。

表の引用元：Access VBA Tips+α 「テーブルやクエリを開く」表4、2015年10月25日閲覧。

さて、レコードセットを開き、作業も終わったらレコードセットとコネクションを閉じます。

adoRS.Close
Set adoRS = Nothing
adoCON.Close
Set adoCON = Nothing

こうやって閉じます。注意すべきなのは即時更新モードでレコード編集しているときは、レコードセットを閉じることができないということです。この場合、Close メソッドを実行するとエラーが発生します。僕はここではまったことがあるので注意。

コネクションは閉じずにレコードセットだけをCloseメソッドで閉じれば、Openメソッドで引き続き別なテーブルやクエリを開くことができます。

参考文献
Access VBA Tips+α 「カーソルの場所を設定する」2015年10月25日閲覧。
Access VBA Tips+α 「テーブルやクエリを開く」2015年10月25日閲覧。
Access VBA Tips+α 「レコードセットを閉じる」2015年10月25日閲覧。