【ExcelVBA】ExcelのマクロからMicrosoft Accessのデータを取得するには

本記事では、マクロからMicrosoft Accessのデータを取得する方法についてご説明します。

Table of Contents

マクロからMicrosoft Accessのデータを取得する方法

マクロからMicrosoft Accessのデータを取得するには、次の流れの通りにコードを書いていきます。

STEP.1

Microsoft Accessへの接続情報取得

Microsoft Accessに接続するための接続情報を取得します。
このMicrosoft Accessに接続するための接続情報は、Microsoft Accessに接続するための「鍵」みたいなものなので必ずコードに記述する必要があります。

STEP.2

Microsoft Accessに接続

接続情報が取得出来たら、その接続情報をもとにMicrosoft Accessに接続します。
Microsoft Accessに接続するには、ConnectionオブジェクトのOpenメソッドを使います。

STEP.3

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

Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、Microsoft Accessのデータの取得に必要です。

STEP.4

データを抽出するSQL文をもとにレコードセットを開く

データを抽出するSQL文をもとにレコードセットを開きます。
レコードセットを開くには、RecordsetオブジェクトのインスタンスのOpenメソッドを使用します。
このOpenメソッドにデータを抽出するSQL文を引数に指定します。

STEP.5

参照したデータを取得

参照したデータを取得します。
参照したデータを取得するには、RecordsetオブジェクトのGetRowsメソッドを使います。
この取得したデータは配列に格納することができます。

目次へ戻る

コードの例

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

Excelのマクロのコード(例)は次の通りです。

    Dim strFileName As String, strSQL As String
    Dim adoCON      As New ADODB.Connection
    Dim adoRS       As New ADODB.Recordset
    
    'カレントディレクトリのデータベースパスを取得
    strFileName = ActiveWorkbook.Path & "\タスク情報.accdb"
    'データを取得するSQL文
    strSQL = "select * from dbo.tbl_userInfo"

    'データベース接続情報の取得
    adoCON.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
                        & "Data Source=" & strFileName & ""
                        
    'データベースに接続する
    adoCON.Open
    
    'オブジェクトの設定（取得用）
    Set adoRS = CreateObject("ADODB.Recordset")
    adoRS.CursorLocation = 3 ' クライアントサイドカーソルに変更
    'レコードセットを開く
    adoRS.Open strSQL, adoCON, adOpenDynamic    

    '通常のselect
    getVal = adoRS.GetRows    

    'クローズ処理
    adoRS.Close
    Set adoRS = Nothing
    adoCON.Close
    Set adoCON = Nothing

コードの解説

最初に見て頂きたいのは22行目です。

RecordsetオブジェクトのOpenメソッドを使い、取得したいテーブルのデータを参照しています。

その参照したデータを取得するには、25行目にあるRecordsetオブジェクトのGetRowsメソッドを使います。

GetRowsメソッドを使うことで、データを取得することができます。(コード(例)では、取得したデータを配列getValに格納しています)

なお、どんなデータを取得したいかは変数strSQLにSQL文を設定し(9行目)、Openメソッドの引数に指定します。(22行目)

また、Microsoft Accessのデータを取得するにはまずMicrosoft Accessに接続しなければいけませんが、マクロからMicrosoft Accessに接続する処理(12行目から16行目)については次の記事で詳しく説明しているので、そちらもあわせてご覧ください。

4月 25, 2021【ExcelVBA】ExcelのマクロからMicrosoft Accessに接続するには

目次へ戻る

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

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

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

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

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

    Dim adoCON      As New ADODB.Connection
    Dim adoRS       As New ADODB.Recordset

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

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

目次へ戻る

【動画】マクロからMicrosoft Accessのデータを取得する参考動画

ツイート内の動画では、会員番号が入ったコンボボックスで会員番号を選ぶとMicrosoft Accessに接続してデータを取得し、取得したデータをExcelのシートにある黄色のセルに設定しています。(動画の0:00～0:10あたり)

ExcelVBAからAccessのDBにアクセスして参照・更新処理ができるか確認してみました😀

以前ExcelVBAとSQL Serverとの連携を確認してみたときと似たようなこと(https://t.co/yi0FE4XM9W)を今回やってみました😄#Excel #VBA #Access pic.twitter.com/TZHe7I8PNg

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) March 15, 2021