ray88’s diary

お仕事で困ったとき用の自分用の覚書

Accessよりテーブルをレコードセットで取得してExcelに取り込む


ConnectionオブジェクトOpenメソッドでAccessDBに接続する

オブジェクト.Open(ConnectionString)

オブジェクト......Connectionオブジェクト
ConnectionString......データベース接続文字列

※Conectionstring(接続文字列の書式)

Provider=プロバイダ名; Data Source=データベースファイル名

プロバイダ名・・・・・接続するデータベースの種類の名前
データベースファイル名・・・・・目的のデータベースのファイル名をパス付で指定

※参考URL(Connectionオブジェクトの主なプロパティとメソッド)
ADO データベースへの接続(Connection オブジェクト) - ray88’s diary
※こちらは定数の各値とプロバイダの設定値を確認できる
データベース(Access 等)に接続(ADO) | ExcelWork.info
RecordsetオブジェクトOpenメソッドでレコードセットを取得する

オブジェクト.Open(Source, ActiveConnection, CursorType, LockType)

オブジェクト・・・・・Recordsetオブジェクト
Soure・・・・・テーブルまたはクエリの名前
ActiveConnection・・・・・Connectionオブジェクト
Cursor Type・・・・・カーソルの種類の定数を指定
LockType・・・・・排他制御の方法の定数を指定

CopyFromRecordsetメソッドで取得したレコードセットをExcelに取り込む

オブジェクト.CopyFromRecordset(Data)

オブジェクト・・・・・取り込み先のセルオブジェクト
Data・・・・・Recordsetオブジェクト

■サンプルコード

Sub テーブル取り込み()
    Dim CN As Object
    Dim RS As Object
    Dim conStr As String
           
    Set CN = createobject("ADODB.Connection")
    Set RS = createobject("ADODB.Recordset")
    
    '接続文字列を設定
    conStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\デスクトップ\test.accdb"
    
    'AccessDBへ接続
    CN.Open Connectionstring:=conStr
    
    'レコードセットを取得
    'CursorType:=1の1はadOpenKeysetの定数の値
    'LockType:=3の3はadLockOptimisticの定数の値
    RS.Open Source:="M_商品", Activeconnection:=CN, CursorType:=1, LockType:=3
    
    'テーブルより取得したレコードセットをExcelへ取り込み
    Sheets("取り込み用").Range("A1").CopyFromRecordset Data:=RS
    RS.Close
    CN.Close
    
    Set RS = Nothing
    Set CN = Nothing    
End Sub