ExcelからADOでAccessのデータを読み込む

ExcelVBAでAccessに接続してデータを読み込んでみます。

Accessファイルの内容
ExcelからAccessのデータを読む
1. 参照設定
2. コード

Accessファイルの内容

Accessのファイルは以下のパスに置かれています。
C:\access-read-test\MenuData.accdb

Accessにはテーブル「T_メニュー」があります。
T_メニューにはメニューデータが記録されています。

テーブルにはID、メニュー名、値段の3つのフィールドがあります。

フィールド名	データ型
ID	オートナンバー型
メニュー名	短いテキスト
値段	数値型

ExcelからAccessのデータを読む

参照設定

ExcelからADOを利用してAccessのデータを読み込むには
参照設定を行う必要があります。

参照設定の方法は以下をご覧ください。

VBAで参照設定を行う

VBAに標準で含まれていない機能があるときは外部のライブラリを参照して、外部ライブラリ内の機能を利用します。この外部ライブラリの機能を利用する設定を、参照設定と言います。

参照設定を起動してMicrosoft ActiveX Data Objects 6.1 Libraryに
チェックを入れます。

コード

ExcelからAccessのデータを読み込んで
シートに書き込むコードは以下になります。

Option Explicit

Sub readAccessData()
     
  Dim dbPath As String
  dbPath = "C:\access-read-test\MenuData.accdb"  'accdbファイルパス
  
  Dim adoCON As New ADODB.Connection
  adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & dbPath & ";"
                            
  adoCON.Open
  
  Dim strSQL As String
  strSQL = "SELECT * FROM T_メニュー"
   
  Dim adoRS  As New ADODB.Recordset
  adoRS.Open strSQL, adoCON, adOpenKeyset, adLockReadOnly
   
  Dim writeSht As Worksheet
  Set writeSht = ThisWorkbook.Worksheets("Sheet1")
  writeSht.UsedRange.Clear
  
  'フィールド名の書き出し
  Dim i As Long
  For i = 0 To adoRS.Fields.Count - 1
      writeSht.Cells(1, i + 1).Value = adoRS.Fields(i).Name
  Next i
  
  'CopyFromRecordsetメソッドで基準セルを指定してデータの書き出し
  writeSht.Range("A2").CopyFromRecordset adoRS
    
  adoRS.Close
  adoCON.Close
  
  Set writeSht = Nothing
  Set adoRS = Nothing
  Set adoCON = Nothing

End Sub

Option Explicit

Sub readAccessData()

Dim dbPath As String

dbPath = "C:\access-read-test\MenuData.accdb" 'accdbファイルパス

Dim adoCON As New ADODB.Connection

adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & dbPath & ";"

adoCON.Open

Dim strSQL As String

strSQL = "SELECT * FROM T_メニュー"

Dim adoRS As New ADODB.Recordset

adoRS.Open strSQL, adoCON, adOpenKeyset, adLockReadOnly

Dim writeSht As Worksheet

Set writeSht = ThisWorkbook.Worksheets("Sheet1")

writeSht.UsedRange.Clear

'フィールド名の書き出し

Dim i As Long

For i = 0 To adoRS.Fields.Count - 1

writeSht.Cells(1, i + 1).Value = adoRS.Fields(i).Name

Next i

'CopyFromRecordsetメソッドで基準セルを指定してデータの書き出し

writeSht.Range("A2").CopyFromRecordset adoRS

adoRS.Close

adoCON.Close

Set writeSht = Nothing

Set adoRS = Nothing

Set adoCON = Nothing

End Sub

readAccessDataを実行するとフィールド名とデータをSheet1に書き出します。