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

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


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

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

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


###Excelに直接テーブルを持ってくる
```vb
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という関数とした。

```vb
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を以下のように変更した。

```vb
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を挟むだけでいける。

```vb
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値はありふれてるので、もう原始的な方法を使うしかない。

↓こういうやつ


```vb
    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についてもう少し調べたら、もっと簡単な方法があったのかもしれない。