便利じゃなければプログラムじゃ無い!
仕事が楽になって業務効率がUPするシステムを提供!

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

2012/8/3 (金) • Access, Excel, VBA

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

ADOを使ってAccessのレコードを利用する

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



Accessのテーブルを作成

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

■入力内容
テーブルの入力内容

■テーブルデザイン
テーブルデザイン

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

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

参照設定をクリック

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

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ソースの解説

  1. Excelブックと同じフォルダ内にあるAccess[sample.accdb]のフルパスを取得する。
    10行目でActiveWorkbook.Pathを使いExcelのフォルダを取得します。

    odbdDB = ActiveWorkbook.Path & "\sample.accdb"
    
  2. データベースに接続します。
    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
    
  3. レコードセットを開きます。
    21行目のOpenメソッドでSQL(18行目)を実行しレコードセットを開きます。

    adoRS.Open strSQL, adoCON, adOpenDynamic
    

    パラメーター等の解説はmsdnをご参考下さい。

  4. テーブル内のデータを全て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
    
  5. 最後にデータベースをクローズします。
    Closeメソッドを使いレコードセットとADOコネクションオブジェクトを閉じます。
    また、それぞれの変数にNothingをセットしてクリアします。

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

以上、今回はExcelからAccessにADOを使って接続する方法でした。

今回のサンプルファイルは以下のリンクからダウンロード可能です。

タグ
, , , , ,

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

  1. […] ADOを使ってExcelからAccessデータを利用してみよう | Excel・Accessで楽々シゴト!INFITH VBA Lab ExcelVBAに興味をお持ちの方は、入門記事もありますのでよろしければどうぞ!ExcelVBA入門第0回 始 […]