Excel VBAを用いてACCESSクエリを実行したい

Question

前提

ACCESS上でクエリを実行する際に、添付画像のような画面でドライバに接続してから実行結果を取得しています。
このクエリの実行結果をExcel VBAで処理したいため、VBAでACCESSに接続し、クエリを実行したいと考えています。

実現したいこと

Excel VBAを用いて、Oracle ODBCの接続込でACCESSクエリを実行したいです。
同じACCESSファイル内には、Oracle ODBCの接続なしで実行できるクエリもありますが、そのクエリは下記の該当のソースコードで実行できます。今回のクエリも同様にVBAで実行して、結果をExcelファイルに貼り付けたいです。

該当のソースコード

Private Sub Sample()

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    
    Dim count As Integer
    
    ' コネクションをセットし、Accessを開く
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Ace.OLEDB.12.0;"
    ' Accessファイルを設定する
    cnn.Open "C:\sample.accdb"
 
    ' コマンドをセット
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    
    cmd.CommandText = "クエリ1"  ' クエリを設定
    
    'レコードセットをセットし、パラメータを指定して実行
    Set rst = New ADODB.Recordset
    Set rst = cmd.Execute()

    'レコードを貼り付ける
    Sheets("新規登録アイテム").Range("A" & count + 1).CopyFromRecordset rst

End Sub

試したこと

ACCESSファイルの接続の際に、パスワードを指定してみましたがうまくいきませんでした。

cnn.Provider = "Microsoft.Ace.OLEDB.12.0;" & "Jet OLEDB:Database Password=Pass;"

Comments

[sazi]

具体的にどのように上手くいっていないのですか？
「Oracle ODBCの接続」ウインドウすら表示されない状況でしょうか？

[gymgym]

VBAは実行でき、エラー画面などは特に出ません。しかし、クエリの実行結果がエクセルに貼り付けられず、処理が終了してしまいます。「Oracle ODBCの接続」ウインドウすら表示されないです。
Access上では正常にクエリは実行できます。

[sazi]

仮に「Oracle ODBCの接続」ダイアログウインドウが表示されたとして、そのウインドウにパスワードを自動で入力するような動きをしないと出来ませんので、Accessが今の作りのまま経由するというのは無理でしょうね。

Answer 1

ACCESSのクエリーの内容をVBAに展開して実行する事をお薦めします。
以下参考
Excel VBAからODBCを使ってデータを簡単に取得する

Comments

[gymgym]

ありがとうございます。想定通り実行できました。