【Excel VBA】Accessのデータベースに接続して、テーブルを取得する方法

こんにちは。IMMRです。

Excel VBAで下記のようなAccessで作成したデータベースを参照する方法を説明します。

図１　DB作成場所

↓

図２　DB内容

１．設定方法

VBAでAccessで作成したデータベースを参照するには、一か所だけ設定が必要になります。

手順は下記のとおりです。

１　Visual　Basicをクリック

図３　設定１

↓

２　「ツール」の「詳細設定」をクリック

図４　設定２

↓

３　下記に示す「Microsoft　Active　Data　Objects　６．１　Library」にチェックを入れてOKをクリックします。

図５　設定３

２．Accessのデータベースに接続して、テーブルを取得する記載方法

まず、AccessのDBに接続するために

①「Connection」と「Recordset」の宣言

接続するための「Connection」オブジェクトと

DBのレコードを取得するために「Recordset」オブジェクトの宣言を行います。

記載方法１

Dim Connectionのオブジェクト名 As New ADODB.Connection
Dim Recordsetのオブジェクト名 As New ADODB.Recordset

②「プロバイダー」と「DBが投稿されているバス」の設定

つぎにAccessのDBに接続と接続します。

DBと接続するのですが、

プロバイダーとDBが投稿されているパスが必要になります。

プロバイダーはAccessのバージョンによって異なります。

プロバイダー

　・Access２００７，　２０１０は、　Microsoft.ACE.OLEDB.12.0

　・Access２０００，　２００２，　２００３は、　Microsoft.Jet.OLEDB.4.0

そして、DBが投稿されているバスを記載します。

記載方法は下記のとおりです。

記載方法２

Connectionのオブジェクト名.Open _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=DBが投稿されているバス;"

記載方法１宣言した「Connection」オブジェクトを使用します。

そして、「プロバイダー」と「DBが投稿されているバス」を記載して、

DBとの接続を行います。

※ここでは、Access２００７，　２０１０のプロバイダーを使用しています。

③「Connection」と「Recordset」の設定

次にDBのテーブルを開くのでテーブル名の記載を行います。

記載方法１宣言した「Connection」オブジェクトと「Recordset」オブジェクトを使用します。

「Recordset」に接続したDBのでテーブルを取得します。

記載方法３

Recordsetのオブジェクト名.Open "テーブル名", Connectionのオブジェクト名

④「Recordset」からの取得

今度は、記載方法３で取得したRecordsetのオブジェクト名つまり、取得したテーブルをセルにセットします。

ここではセル（A1）に取得テーブルを張り付けています。

記載方法４

Range("A1").CopyFromRecordset Data:=Recordsetのオブジェクト名

⑤DBの切断

そして、取得が終わったらDBを切断します。

記載方法５

Connectionのオブジェクト名.Close

⑥「Connection」の解放

そして最後に

接続するための「Connection」オブジェクトを解放します。

記載方法６

Set Connectionのオブジェクト名 = Nothing

いままでの流れがDBに接続して、テーブルを取得する流れです。

では、今まで説明してきた記載方法を結合したサンプルプログラムをみていきましょう。

３．Accessのデータベースに接続して、テーブルを取得する（サンプルプログラム）

では、図１　DB作成場所にあるにある

図２　DB内容　のテーブルを取得して、セル（A1）にコピーするサンプルプログラムです。

サンプルプログラム

Sub test1()

    Dim Con_DB As New ADODB.Connection
    Dim Rec_TBL As New ADODB.Recordset

    Con_DB.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\access\DB_名簿.accdb;"

    Rec_TBL.Open "生徒名簿", Con_DB

    Range("A1").CopyFromRecordset Data:=Rec_TBL

    Con_DB.Close

    Set Con_DB = Nothing

End Sub

４．Accessのデータベースに接続して、テーブルを取得する（サンプルプログラム）の実行結果

実行結果は下記のようになります。

図６　実行結果

図２　DB内容のものと同じであることが確認できます。

図２　DB内容

Accessのデータベースに接続して、テーブルを取得することができました。