【ExcelVBA】Recordsetで取得したAccessのテーブルデータをシート上に設置されたListboxに表示させる方法

この記事では、Recordsetで取得したAccessのテーブルデータをシート上に設置されたListboxに表示させる方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】Recordsetで取得したAccessのテーブルデータをシート上に設置されたListboxに表示させる実際の動き

本題に入る前に、まずは次の動画をご覧ください。

AccessのテーブルデータをRecordsetで取得し、シート上に設置されたListboxに表示させています。

マクロ作成の流れ

STEP.1

Accessのデータベースファイルに接続する

Accessのファイルに接続します。

STEP.2

Recordsetオブジェクトのインスタンスを生成する

Recordsetオブジェクトのインスタンスを生成します。

STEP.3

データベースファイルからテーブルデータを取得する

データベースファイルからテーブルデータを取得します。

STEP.4

STEP.3で取得したデータをListboxに表示させる

STEP.3で取得したデータをListboxに表示させます。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub test()

    Dim DBName      As String               'データベース名
    Dim connDB      As String               'データベース接続情報
    Dim sqlStr      As String               'SQL文
    Dim adoCON      As New ADODB.Connection 'Connection用変数
    Dim adoRS       As ADODB.Recordset      'レコードセット用変数
        
    'Accessのデータベースファイルパスを取得
    DBName = ActiveWorkbook.Path & "\" & "0117.mdb"
 
    'データベース接続情報の取得
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & DBName & ""
    
    'Accessに接続する
    adoCON.Open connDB
 
    'Recordsetオブジェクトのインスタンスを生成する
    Set adoRS = CreateObject("ADODB.Recordset")
            
    'Accessからテーブルデータを取得するSQL文を作成
    sqlStr = "select * from tbl_datalist "
    sqlStr = sqlStr & " ORDER BY 1 ASC"
    
    'Accessからテーブルデータを取得する
    adoRS.Open sqlStr, adoCON

    With ListBox1
        
        'データの数分列を表示する
        .ColumnCount = 2
        
        'Accessのテーブルから取得したデータをListboxに表示させる
        .Column = adoRS.GetRows
        
    End With

    'Connectionを閉じる
    adoCON.Close
    
    Set adoCON = Nothing
    Set adoRS = Nothing

End Sub

注目すべきコード①

最初に見て頂きたいのは15行目から20行目です。

    'データベース接続情報の取得
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                            & "Data Source=" _
                            & DBName & ""
    
    'Accessに接続する
    adoCON.Open connDB

15行目から17行目でAccessのデータベースファイルに接続するのに必要な情報を取得してConnectionStringプロパティに設定し、その情報をもとに20行目でAccessのデータベースファイルに接続します。

注目すべきコード②

次に見て頂きたいのは23行目です。

    'Recordsetオブジェクトのインスタンスを生成する
    Set adoRS = CreateObject("ADODB.Recordset")

23行目でRecordsetオブジェクトのインスタンスを生成します。

インスタンスが生成されると、Recordsetを扱うことができます。

注目すべきコード③

次に見て頂きたいのは26行目から30行目です。

    'Accessからテーブルデータを取得するSQL文を作成
    sqlStr = "select * from tbl_datalist "
    sqlStr = sqlStr & " ORDER BY 1 ASC"
    
    'Accessからテーブルデータを取得する
    adoRS.Open sqlStr, adoCON

26行目では、Accessのテーブルデータを取得するSQL文を用意します。

30行目では、先ほどのSQL文をOpenメソッドの引数に指定してOpenメソッドを実行し、Accessからテーブルデータを取得します。

Openメソッドの第2引数には、注目すべきコード①で用意したデータベース接続情報が格納された変数adoCONを指定します。

注目すべきコード④

次に見て頂きたいのは32行目から40行目です。

    With ListBox1
        
        'データの数分列を表示する
        .ColumnCount = 2
        
        'Accessのテーブルから取得したデータをListboxに表示させる
        .Column = adoRS.GetRows
        
    End With

上記は、Listboxに関する設定を行っているコードです。

38行目では、GetRowsメソッドを実行してRecordsetで取得したAccessのテーブルデータをListboxのColumnプロパティに設定することで、シート上に設置されたListboxにAccessのテーブルデータを表示させています。

目次へ戻る

動作確認

今回の動作確認用にAccessのデータベースファイルと、そのAccessのデータベースファイルに都道府県の名称が保存されているテーブル「tbl_datalist」を用意しました。

この都道府県の名称を、Excelのシート上に設置されたListboxに表示させます。

マクロ実行前

下のExcelは、マクロを実行する前の状態です。

マクロ実行後

マクロを実行すると、下の画像のように都道府県の名称がExcelのシート上に設置されたListboxに表示されています。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelマクロのコードの8行目「ADODB.Connection」と9行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim adoCON      As New ADODB.Connection 'Connection用変数
    Dim adoRS       As ADODB.Recordset      'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、Recordsetで取得したAccessのテーブルデータをシート上に設置されたListboxに表示させる方法についてご説明しました。

Accessのテーブルデータをシート上に設置されたListboxに表示させたい場合、Recordsetを使うと簡単に表示させることができます。是非参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間無料体験」はこちら

目次へ戻る