【Excel,Access】ExcelとAccess間でのRecordSetの取り扱いが面倒だった

ExcelとAccessを使用したシステムを構築中に少しハマった話。
主に以下のことについて。

• RecordSetオブジェクトを閉じるタイミング
• RecordSetのGetRowsのこと
• 配列の縦横入れ替え時のTranspose関数

###Access内の「fruit」テーブル
サンプルとしてこういうテーブルを用意。

ID	名称	色	味
1	りんご	赤	美味い
2	バナナ	黄	美味い
3	ブドウ	紫
4	みかん	オレンジ	美味い

###Excelに直接テーブルを持ってくる

Sub test()

    Dim adoCn As Object: Set adoCn = CreateObject("ADODB.Connection")
    Dim adoRs As Object: Set adoRs = CreateObject("ADODB.Recordset")

    Dim filePath As String
    filePath = "Accessファイルのパス"

    Dim SQL As String
    SQL = "SELECT * FROM fruit"
    
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";"
    adoRs.Open SQL, adoCn

    Range("A1").CopyFromRecordset adoRs

    adoRs.Close: Set adoRs = Nothing
    adoCn.Close: Set adoCn = Nothing

End Sub

ワークシート上にデータを持ってくる一番シンプルなコードだが、
別のプロシージャでは CopyFromRecordset ではなく任意のセルに任意の値を差し込みたいとか
いくつかの分岐が欲しくなってきた。

そこで、Accessに繋ぐ部分だけをGET_RECORDという関数とした。

Function GET_RECORD(filePath As String, SQL As String) As Recordset
    
    Dim adoCn As Object: Set adoCn = CreateObject("ADODB.Connection")
    Dim adoRs As Object: Set adoRs = CreateObject("ADODB.Recordset")
    
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";"
    adoRs.Open SQL, adoCn
    
    Set GET_RECORD = adoRs
    
    adoRs.Close: Set adoRs = Nothing
    adoCn.Close: Set adoCn = Nothing

End Function

GET_RECORDは引数としてDBのファイルパスとSQLを渡すと、結果のRecordSetを返してくれる。
あとは各プロシージャがRecordSetを用途に合わせて処理するという感じだ。
だが、これがうまく動かなかった。

##問題①　実行時エラー"3704"
GET_RECORDを使用するマクロを実行すると以下のエラーとなった。
実行時エラー"3704" オブジェクトが閉じている場合は、操作は許可されません。

閉じているオブジェクトってどれのことだろう…。

もしかして、もしかしてだった。
この2行をコメントアウトすると正しく動作した。
adoRs.Close: Set adoRs = Nothing
adoCn.Close: Set adoCn = Nothing

別のRecordSet変数が受け取ったのを見届けてNothingしました。
受け取ったほうのRecordSetもNothingされました、ということだった。

正直RecordSetについて詳しくは知らないんだけど、きっと値そのものではないんだな…

Nothingしないのは気持ちが悪い。
GET_RECORDがOPENしたものはGET_RECORDがCLOSEしてNothingしておきたい。

そこで、2次元配列で返す方針に変えた。

##問題②　GetRowsによる縦横問題
GET_RECORDを以下のように変更した。

Function GET_RECORD(filePath As String, SQL As String) As Variant
    
    Dim adoCn As Object: Set adoCn = CreateObject("ADODB.Connection")
    Dim adoRs As Object: Set adoRs = CreateObject("ADODB.Recordset")
    
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";"
    adoRs.Open SQL, adoCn
    
    GET_RECORD = adoRs.GetRows
    
    adoRs.Close: Set adoRs = Nothing
    adoCn.Close: Set adoCn = Nothing

End Function

RecordSet.GetRowsを使うことで、戻り値は2次元配列となった。
配列化したので、当然Nothingしても永続化されている。

さて、あとは配列を煮るなり焼くなりなのだが、次の問題が発生した。

配列の中身は以下のようになっていた。

ID	1	2	3	4
名称	りんご	バナナ	ブドウ	みかん
色	赤	黄	紫	オレンジ
味	美味い	美味い		美味い

そうなるの…。

これでもまぁ取りたいものは取れてるからいいんだけど、気持ち悪い。
何よりGET_RECORDは汎用的に使う関数にしたいので、
縦横が入れ替わった配列が返ります！なんて言えない。

##問題③　Transpose関数
配列の縦横を入れ替えるときはTranspose関数だ。
GET_RECORDが返す前にいったんTransposeを挟むだけでいける。

Function GET_RECORD(filePath As String, SQL As String) As Variant
    
    Dim adoCn As Object: Set adoCn = CreateObject("ADODB.Connection")
    Dim adoRs As Object: Set adoRs = CreateObject("ADODB.Recordset")
    
    adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";"
    adoRs.Open SQL, adoCn
    
    Dim tmp As Variant
    tmp = adoRs.GetRows
    GET_RECORD = WorksheetFunction.Transpose(tmp)
    
    adoRs.Close: Set adoRs = Nothing
    adoCn.Close: Set adoCn = Nothing

End Function

…いけませんでした。

これを実行してみると、
実行時エラー"13"　型が一致しません。 となった。

厳密には出来ていたんだけど、特定のテーブルの時だけエラーとなった。
テーブルの違いを見ていくと、どうやらデータにNull値があるとエラーになってるっぽい。
対象としているDBではNull値はありふれてるので、もう原始的な方法を使うしかない。

↓こういうやつ

    For i = 0 To UBound(tmp1, 1)
        For j = 0 To UBound(tmp1, 2)
            tmp2(i, j) = tmp1(j, i)
        Next j
    Next i

これでやっとRecordSetを良い感じに扱えるようになった。

RecordSetについてもう少し調べたら、もっと簡単な方法があったのかもしれない。