えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

トップ > VBA > ADODB.Recordset活用のススメ

ADODB.Recordset活用のススメ

VBA

ADOは ActiveX Data Objectsの略で、Microsoftが提供するデータベースへアクセスのためのライブラリです。

今回はADOのRecordsetをデータベースを扱わないExcelでも使うと便利なときもあるよ話をします。

使用方法

使用するときは、 ADOは参照設定で「Microsoft ActiveX Data Objects 6.1 Library」にチェックを入れることで使用できます。

  Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

参照設定をしない場合は、CreateObjectで書くこともできます。

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

Recordsetテーブルの作り方

特徴

Recordsetでは、データベースで使われる”ちゃんとしたテーブル”を作成するため、フィールド（列）を定義してからデータの追加を行います。

フィールドの追加

フィールドを追加するときは、Recordsetの Fields.Append を使用します。

Sub Append(Name As String, Type As DataTypeEnum, [DefinedSize As Long], [Attrib As FieldAttributeEnum = adFldUnspecified], [FieldValue])

  1. Name: 新しいフィールドの名前を指定する String 値です。Fields コレクション内で一意である必要があります。
  2. Type: 新しいフィールドのデータ型を指定する DataTypeEnum 値です。例えば、adInteger や adVarChar などがあります。既定値は adEmpty です。
  3. DefinedSize (省略可能): 新しいフィールドの定義されたサイズを指定する Long 値です。文字数やバイト数で指定します。省略した場合、データ型から派生した既定値が使用されます。
  4. Attrib (省略可能): 新しいフィールドの属性を指定する FieldAttributeEnum 値です。例えば、adFldUpdatable や adFldKeyColumn などがあります。既定値は adFldDefault です。
  5. FieldValue (省略可能): 新しいフィールドの初期値を指定する Variant 値です。省略した場合、フィールドには Null 値が設定されます。
With rs
    .Fields.Append "ID", adInteger
    .Fields.Append "Name", adVarChar, 50
    .Fields.Append "Age", adInteger
    .Open
End With

データの追加方法1

Recordsetでは、rsの中に「現在読み書きしている行」の状態が書き込まれているため、配列のようにインデックスが存在しないことに注意が必要です。

例えば、下図のようにAddNewするとテーブルに1行追加され、新しく作成した行にカーソルが移動します。

書き込みをした後は、Updateメソッドを実行してようやく確定になります。

rs.AddNew
rs.Fields("ID").Value = 1
rs.Fields("Name").Value = "John Doe"
rs.Fields("Age").Value = 30
rs.Update

データの追加方法2

AddNewには省略可能な引数が2つあり、これを指定することで1行で行の追加が可能です。

  1. FieldList (省略可能):
    • 新しいレコード内のフィールドの名前、名前の配列、または序数位置を指定します。
    • 省略した場合、Fields コレクションのすべてのフィールドが対象となります。
  2. Values (省略可能):
    • 新しいレコード内のフィールドの値、または値の配列を指定します。
    • FieldList が配列の場合、Values も同じ数のメンバーを含む配列である必要があります。
rs.AddNew Array("ID", "Name", "Age"), Array(1, "John Doe", 30)
rs.Update

Recordsetのフィールド部の出力方法

Recordset.Fieldsには、一括でセルへ出力したり配列に変換してくれる機能はありません。

煩雑にはなりますが、ForやForEachで個別にループする必要があります。

フィールド出力方法1 : 列順に個別にアクセス

Dim i As Long
For i = 1 To rs.Fields.Count
    Cells(1, i).Value = rs.Fields(i - 1).Name
Next i

フィールド出力方法2 : 列をDictionaryに格納してから一気に出力

Dim dic As New Dictionary, fld: For Each fld In rs.Fields: dic(fld.Name) = fld.Type: Next
Cells(10, 1).Resize(1, rs.Fields.Count) = dic.Keys()

Recordsetのデータ部の出力方法

Recordsetのデータをセルへ出力するには、個別にアクセスする方法の他に、一括で出力する方法と、配列変数へ変換してからセルへ出力する方法があります。

既に説明したように、Recordsetにはカーソル位置の状態が格納されているため、現在位置より後のデータだけが出力対象となるため、 rs.MoveFirst を必ず入れましょう。

データ出力方法1 : 列名で個別に出力

rs.MoveFirst
Do While Not rs.EOF
    Debug.Print rs.Fields("ID").Value & ", " & rs.Fields("Name").Value & ", " & rs.Fields("Age").Value
    rs.MoveNext
Loop

データ出力方法2 : Range.CopyFromRecordsetで全データ一気に出力

異なるライブラリにも関わらずExcel.Range型には CopyFromRecordset という関数があり、ADODB.Recordset型を渡すことができる機能があります。

Function CopyFromRecordset(Data As Unknown, [MaxRows], [MaxColumns]) As Long

引数

  1. Data (必須):
    • 出力する Recordset オブジェクトを指定します。
    • Recordsetの現在のカーソル以降が出力対象となる点に注意が必要です。
    • この引数は省略できません。
  2. MaxRows (省略可能):
    • ワークシートにコピーするレコードの最大数を指定します。
    • この引数を省略すると、Recordset オブジェクトのすべてのレコードがコピーされます。
  3. MaxColumns (省略可能):
    • ワークシートにコピーするフィールドの最大数を指定します。
    • 実在するフィールド数より大きな数字を指定しても問題ありません。
    • 0以下を指定するとエラーになります。
    • この引数を省略すると、Recordset オブジェクトのすべてのフィールドがコピーされます。

例えば下のプログラムを実行すると、A2を基点として全てのデータをセルに出力することができます！

rs.MoveFirst
Cells(2, 1).CopyFromRecordset rs

データ出力方法3 : GetRowsで2次元配列を取得してから一気に出力

Recordset には Recordset.GetRows() という関数があります。

引数

  1. NumRows (省略可能): 取得する行数を指定します。省略した場合、レコードセットのすべての行が取得されます。
  2. Start (省略可能): データの取得を開始するレコードのブックマークを指定します。省略した場合、現在のレコードから取得が開始されます。
  3. Fields (省略可能): 取得するフィールドを指定します。単一のフィールド名または位置、または複数のフィールド名や位置の配列を指定できます。省略した場合、すべてのフィールドが取得されます。

出力前に変数に格納できるので、メモリ上で計算ソースとしても使いやすいです。

一方で、GetRows()の結果は行と列が逆転した結果が返ってくるのですが、WorksheetFunction.Transpose関数などを使用して回転させなければなりません。

rs.MoveFirst
Dim vv
vv = WorksheetFunction.Transpose(rs.GetRows())
Cells(11, 1).Resize(rs.RecordCount, rs.Fields.Count) = vv

Transpose利用上の注意

ところがTranspose関数にあるいろいろな制限の都合でエラーになる可能性がかなり高いです。結果的に同様の回転関数を自作する必要があります。

  • ※TransposeはNull値の混在がNG
  • ※Transposeは行数が多いときもNG

ActiveXが廃止になるらしいという話について

ADOは ActiveX Data Objectsの略ですが、ActiveXという言葉は非常に幅広い意味が含まれているので、対象になることはないと考えています。

10月発売の「Office 2024」でActiveXが既定無効に ～「Microsoft 365」は2025年4月から - 窓の杜

https://qiita.com/yaju/items/1aa5b322d4dac893a5e9

完成コード

Option Explicit

Function CreateRecordsetInMemory() As ADODB.Recordset
    Dim rs As ADODB.Recordset

    Rem 新しいRecordsetオブジェクトを作成
    Set rs = New ADODB.Recordset

    Rem フィールドを追加
    With rs
        .Fields.Append "ID", adInteger
        .Fields.Append "Name", adVarChar, 50
        .Fields.Append "Age", adInteger
        .Open
    End With

    Rem データを追加
    rs.AddNew
    rs.Fields("ID").Value = 1
    rs.Fields("Name").Value = "John Doe"
    rs.Fields("Age").Value = 30
    rs.Update

    rs.AddNew
    rs.Fields("ID").Value = 2
    rs.Fields("Name").Value = "Jane Smith"
    rs.Fields("Age").Value = 25
    rs.Update

    rs.AddNew
    rs.Fields("ID").Value = 3
    rs.Fields("Name").Value = "xxxxxxx"
    rs.Fields("Age").Value = 55
    rs.Update
    
    Rem データを追加（一括）
    rs.AddNew Array("ID", "Name", "Age"), Array(1, "John Doe", 30)
    rs.Update
    
    Set CreateRecordsetInMemory = rs
End Function

Function WriteRecordset(rs As ADODB.Recordset)

    Rem ----------------------------
    
    Rem データ出力方法1 : 列名で個別にアクセス
    rs.MoveFirst
    Do While Not rs.EOF
        Debug.Print rs.Fields("ID").Value & ", " & rs.Fields("Name").Value & ", " & rs.Fields("Age").Value
        rs.MoveNext
    Loop
    
    Rem ----------------------------
    Cells.ClearContents
    rs.MoveFirst
    
    Rem フィールド出力方法1 : 列順に個別にアクセス
    Dim i As Long
    For i = 1 To rs.Fields.Count
        Cells(1, i).Value = rs.Fields(i - 1).Name
    Next i
    
    Rem データ出力方法2 : Range.CopyFromRecordsetで全データ一気に出力
    Rem メリット : 超シンプル
    Cells(2, 1).CopyFromRecordset rs
    
    Rem ----------------------------
    rs.MoveFirst
    
    Rem フィールド出力方法2 : 列をDictionaryに格納してから一気に出力
    Dim dic As New Dictionary, fld: For Each fld In rs.Fields: dic(fld.Name) = fld.Type: Next
    Cells(10, 1).Resize(1, rs.Fields.Count) = dic.Keys()
    
    Rem データ出力方法3 : GetRowsで2次元配列を取得してから一気に出力
    Rem メリット：出力前に加工ができる。計算ソースとしても使いやすい
    Rem デメリット : Transpose互換の回転関数を自作する必要がある
    Rem             ※TransposeはNull値の混在がNG
    Rem             ※Transposeは行数が多いときもNG
    Dim vv
    vv = WorksheetFunction.Transpose(rs.GetRows())
    Cells(11, 1).Resize(rs.RecordCount, rs.Fields.Count) = vv

    Rem ----------------------------
    
    Rem Recordsetを閉じる
    rs.Close
    Set rs = Nothing
End Function