ADOを使ってExcelからAccessデータを利用してみよう

Excelから直接Accessデータベースに接続して、レコードを表示してみましょう。
今回はADOを使ってAccessに接続し、指定したテーブルのレコードを全てExcelに表示するといった、お手軽なサンプルです。

それでは一覧の流れを説明していきます。

Accessのテーブルを作成

今回のサンプルではテーブル名を[T_item]として作成します。テーブルデザインや入力内容は以下の通りです。

■入力内容

■テーブルデザイン

ExcelからAccessのテーブルをADOで読み込む為の設定

Excelを起動し、VBE(Visual Basic Editor)を[Alt]+[F11]で起動し、ツール(T)　→　参照設定(R)　をクリックして下さい。

参照設定のウィンドウが開いたらMicrosoft ActiveX Data Objects 2.X Libraryにチェックを入れます。

テーブルをADOで読み込む為のVBAコード

[データ読込]ボタンのクリック時のVBAは以下の通りです。

Sub DB_Read()
    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 & "\sample.accdb"

    'データベースに接続する
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                        & "Data Source=" & odbdDB & ""
    adoCON.Open

    'DB接続用SQL
    strSQL = "SELECT T_item.* FROM T_item ORDER BY T_item.ID;"

    'レコードセットを開く
    adoRS.Open strSQL, adoCON, adOpenDynamic

    'アクティブなシート名を取得
    wSheetName = ActiveSheet.Name

    'スタート行をセット
    i = 3

    'テーブルの読み込み
    Do Until adoRS.EOF
        With Worksheets(wSheetName)
            .Cells(i, 1).Value = adoRS!ID
            .Cells(i, 2).Value = adoRS!商品名
            .Cells(i, 3).Value = adoRS!品番
            .Cells(i, 4).Value = adoRS!単価
            .Cells(i, 5).Value = adoRS!入数
        End With
        i = i + 1
        adoRS.MoveNext
    Loop

    'クローズ処理
    adoRS.Close
    Set adoRS = Nothing
    adoCON.Close
    Set adoCON = Nothing

End Sub

VBAソースの解説

Excelブックと同じフォルダ内にあるAccess[sample.accdb]のフルパスを取得する。
10行目でActiveWorkbook.Pathを使いExcelのフォルダを取得します。
```
odbdDB = ActiveWorkbook.Path & "\sample.accdb"
```
データベースに接続します。
13～15行目でAccessデータベースに接続します。
尚、今回のアクセスのバージョンは2007以降（2016まで確認）の為、providerにMicrosoft.ACE.OLEDB.12.0を指定しましたが、2000～2003の場合はMicrosoft.Jet.OLEDB.4.0を指定して下さい。
```
adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                    & "Data Source=" & odbdDB & ""
adoCON.Open
```
レコードセットを開きます。
21行目のOpenメソッドでSQL（18行目）を実行しレコードセットを開きます。
```
adoRS.Open strSQL, adoCON, adOpenDynamic
```
パラメーター等の解説はmsdnをご参考下さい。

テーブル内のデータを全てExcelに出力します。
30～40行目にかけてDo Until Loopを使って繰り返しレコードをExcelに出力します。

Do Until adoRS.EOF  'レコードセットが終了するまで処理を繰り返す
    With Worksheets(wSheetName)
        .Cells(i, 1).Value = adoRS!ID
        .Cells(i, 2).Value = adoRS!商品名
        .Cells(i, 3).Value = adoRS!品番
        .Cells(i, 4).Value = adoRS!単価
        .Cells(i, 5).Value = adoRS!入数
    End With
    i = i + 1       '行をカウントアップする
    adoRS.MoveNext  '次のレコードに移動する
Loop

最後にデータベースをクローズします。
Closeメソッドを使いレコードセットとADOコネクションオブジェクトを閉じます。
また、それぞれの変数にNothingをセットしてクリアします。
```
adoRS.Close: Set adoRS = Nothing
adoCON.Close: Set adoCON = Nothing
```