Accessのテーブルをワークシートに出力する

Accessの指定テーブルをワークシートに出力する方法を紹介します。

Accessのテーブル名と出力始点セルを渡せば動く汎用関数にしましたので、
ただこの処理を行いたいだけなら、本関数を中身を見ずにCallしてもOKです。

ソースコード

' Accessテーブルの取込
Sub Accessの指定テーブルをセルに出力する(Accessファイルパス As String, テーブル名 As String _
    , 出力始点セル As Range, is見出しも出力する As Boolean)
    
    Dim 接続 As Object: Set 接続 = CreateObject("ADODB.Connection")
    接続.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Accessファイルパス & ";"
    
    Dim レコードセット As Object: Set レコードセット = CreateObject("ADODB.Recordset")
    レコードセット.Open "SELECT * FROM " & テーブル名, 接続
    
    ' フィールド名を第1行に出力
    If is見出しも出力する Then
        Dim C As Long
        For C = 1 To レコードセット.Fields.Count
            出力始点セル.Cells(1, C).Value = レコードセット.Fields(C - 1).Name
        Next
    End If
        
    ' 第2行以降に全レコードを出力
    出力始点セル.Cells(IIf(is見出しも出力する, 2, 1), 1).CopyFromRecordset レコードセット
    
    レコードセット.Close: Set レコードセット = Nothing
    接続.Close: Set 接続 = Nothing
    
End Sub

' 読み込むAccessファイルの指定
Dim Accessファイルパス As String
Accessファイルパス = ThisWorkbook.Path & "\Accessテストファイル.accdb"
    
' Accessの「Tデータ」「Mマスタ」テーブルを既存のシートに出力
Call Accessの指定テーブルをセルに出力する _
    (Accessファイルパス, "Tデータ", Worksheets("データ").Range("A2"), False)
Call Accessの指定テーブルをセルに出力する _
    (Accessファイルパス, "Mマスタ", Worksheets("データ").Range("A2"), False)

' 同じく「Tデータ」というテーブルを新規ブックとして出力
Dim ws出力シート As Worksheet
Set ws出力シート = Workbooks.Add.Worksheets(1)
Call Accessの指定テーブルをセルに出力する _
    (Accessファイルパス, "Tデータ", ws出力シート.Range("A1"), True)
    ' ※ 第4引数「is見出しも出力する」をTrueにすれば見出し(Accessで言うところのフィールド名)も出力されます。

解説

まずは汎用関数の仕様ですが、各引数名の通り、

• 「読み込むAccessファイルのファイルパス」と「読み込むテーブル名」
• 「出力始点セル」と「見出し(フィールド名)も出力するか」

を指定して実行すると、Accessのテーブルをそのままワークシートに出力しますので、
この仕様で問題がなければ、中身を見ずにCallして使っていただいてもOKです。

とりあえず全データをWorksheetに出力してしまい、
抽出や加工はExce上でやってしまおうという場合にご使用ください。

コードの中身の説明としては、

1. ADODBでAccessファイルと接続
2. シンプルなSELECT文で全データを取得
3. RangeオブジェクトのCopyFromRecordsetメソッドでシートに出力

という、Excel×SQL文の教科書と言ってよいストレートなコードを記述しています。

ADODBのSELECT文で取得したデータはRecordsetオブジェクトになっており、
これがRange.CopyFromRecordsetで簡単にセルに出力できるのがいいですね。

抽出時にWHERE句を使ってデータを絞ることも当然できますので、
SQLの知識がある方はそのあたりも関数に組み込んでみてください。

逆にシートに出力する以上、データの抽出はAutoFilterなどに任せてもOKです。

SQLは「全データ取得」だけをこの関数で行うだけにして、
Excelの各機能で抽出や集計加工を行うのももちろん可能です。

このあたりは、ExcelとDBの知識が深い方の処理を採用してください。