【ExcelVBA】Accessのデータベースからクエリの名称を全て取得する方法

この記事では、Accessのデータベースからクエリの名称を全て取得する方法についてご説明します。

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

Table of Contents

【動画】Accessのデータベースからクエリの名称を全て取得する実際の動き

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

QueryDefオブジェクトのインスタンスを生成し、Accessのデータベースからクエリの名称を取得しています。

クエリの名称はNameプロパティを使い、取得したクエリの名称をセルに出力しています。

目次へ戻る

マクロ作成の流れ

STEP.1

Accessのデータベースファイルの格納先を取得する

Accessのデータベースファイルの格納先を取得します。

STEP.2

STEP.1のAccessのデータベースを開く

STEP.1のAccessのデータベースを開きます。

STEP.3

Accessのデータベースからクエリの名称を取得する

Accessのデータベースからクエリの名称を取得します。

目次へ戻る

コードの例

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

Option Explicit

Sub test()

    Dim accDBNM     As String                       'Accessのデータベースファイル
    Dim db          As Object                       'Databaseオブジェクト変数
    Dim dbe         As Object                       'DBEngineオブジェクト変数
    Dim qdf         As Object                       'QueryDefオブジェクト用変数
    Dim cnt         As Integer                      'カウンタ用変数
    
    'カレントディレクトリのデータベースパスを取得
    accDBNM = ActiveWorkbook.Path & "\" & "0172.mdb"
    
    'カウンタを初期化する
    cnt = 1
    
    'DBEngineオブジェクトのインスタンスを生成する
    Set dbe = New DAO.DBEngine
    
    'Accessのデータベースを開く
    Set db = dbe.OpenDatabase(accDBNM)
    
    'クエリの数だけ処理を繰り返すループ
    For Each qdf In db.QueryDefs
    
        'クエリの名称をセルに出力する
        Worksheets("work").Range("A" & cnt).Value = qdf.Name
        
        cnt = cnt + 1
    
    Next
    
    'データベースを閉じる
    db.Close
    
    '後処理
    Set db = Nothing
    Set dbe = Nothing
    Set qdf = Nothing

End Sub

注目すべきコード①

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

    'カレントディレクトリのデータベースパスを取得
    accDBNM = ActiveWorkbook.Path & "\" & "0172.mdb"

以上のコードは、Accessのデータベースファイルの格納先を取得するコードです。

注目すべきコード②

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

    'DBEngineオブジェクトのインスタンスを生成する
    Set dbe = New DAO.DBEngine
    
    'Accessのデータベースを開く
    Set db = dbe.OpenDatabase(accDBNM)

以上のコードは、Accessのデータベースを開く処理のコードです。

18行目でDBEngineオブジェクトのインスタンスを生成し、そのインスタンスのOpenDatabaseメソッドの引数にデータベース(のフルパス)を引数に指定して実行します。

正常にOpenDatabaseメソッドが実行されると、Accessのデータベースにあるクエリの名前が取得できるようになります。

注目すべきコード③

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

    'クエリの数だけ処理を繰り返すループ
    For Each qdf In db.QueryDefs
    
        'クエリの名称をセルに出力する
        Worksheets("work").Range("A" & cnt).Value = qdf.Name
        
        cnt = cnt + 1
    
    Next

以上のコードは、Accessのデータベースにあるクエリの数だけ、そのクエリの名前をExcelのシートに出力する処理のコードです。

24行目のコードは、クエリの数だけ処理を繰り返すループのFOR文です。

27行目のコードは、Nameプロパティからクエリの名称を取得してExcelのシートに出力しています。

目次へ戻る

動作確認

マクロ実行前

以下のAccessのデータベースにクエリが登録されており、このクエリの名前をExcelのシートに出力します。

マクロ実行後

マクロの実行が正常に完了すると、以下のようにクエリの名前が出力されました。

目次へ戻る

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

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

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

microsoft office 15.0 access database engine object library(ACEDAO.DLL)

なぜ必要かというと、Excelのマクロのコードの18行目の「DAO.DBEngine」というオブジェクトが「ACEDAO.DLL」というファイルを参照するからです。

    'DBEngineオブジェクトのインスタンスを生成する
    Set dbe = New DAO.DBEngine

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

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

目次へ戻る

最後に

本記事では、Accessのデータベースからクエリの名称を全て取得する方法についてご説明しました。

Accessのクエリの名称を取得したい時は本記事を参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら

目次へ戻る