VBA:ExcelからAccessの複数パラメータ付きのアクションクエリを実行し、セルにコピーする

2021年6月14日
2021年6月30日
VBA
Access, ADO, Excel, VBA, アクションクエリ, 複数パラメータ

VBA

1 Excel VBAでAccessの複数パラメータ付きアクションクエリを実行し、セルにコピーする
2 パラメータ付きを実行するために、Accessのクエリを設定する

Excel VBAでAccessの複数パラメータ付きアクションクエリを実行し、セルにコピーする

前回は、Accessでパラメータ付きのアクションクエリをExcel VBAを利用して実行する方法を説明しました。

VBA:ExcelからAccessのパラメータ付きのアクションクエリを実行し、セルにコピーする

Excel VBAでAccessのパラメータ付きアクションクエリを実行し、セルにコピーする前回は、AccessのアクションクエリをExcel VBAを利用して実行する方法を説明しました。 [sitecard subtitle=関連[…]

毎度のことなんですが、前回に実行している途中、パラメータ付きが複数あったらどうやって実行するのかという興味が出てきました。

今回は、Accessの複数パラメータ付きのアクションクエリをExcel VBAを利用して実行する方法を調べてみました。

VBAを実行する前に、「Microsoft ActiveX Data Objects 6.1 Libraly」または「Microsoft ActiveX Data Objects 2.X Libraly」を参照設定します。

参照設定については、以下で説明しています。

VBA:事前バインディングと実行時バインディングの違い

事前バインディングと実行時バインディングの違い VBAのバインディングとは、外部のオブジェクトの機能をVBAで利用できるようにすることです。バインディングは2通りの方法があり、実行時に利用できるようにする実行時バインディング(遅延[…]

ExcelのVBAを利用して、Accessのアクションクエリを複数パラメータ付きで実行し、セルにコピーする基本的なコードは以下のような感じになります。

複数パラメータは、19行目でArrayを利用してパラメータを設定しています。

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    
    ' コネクションをセットし、Accessを開く
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.Ace.OLEDB.12.0;"
    ' 任意のAccessファイルを設定する
    cnn.Open ThisWorkbook.Path & "\ado3.accdb"

    ' コマンドをセット
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    
    cmd.CommandText = "Q_data"  ' クエリを設定
    
    'レコードセットをセットし、パラメータを指定して実行
    Set rst = New ADODB.Recordset
    Set rst = cmd.Execute(Parameters:=Array("%札幌%", "%南%"))
    
    If rst.EOF Then
        MsgBox "データがありません。"
    Else
        'レコードをA1セルへ貼り付ける
        Range("A1").CopyFromRecordset rst
    End If
    
    ' 閉じる
    rst.Close
    cnn.Close
    
    ' 解放する
    Set cmd = Nothing
    Set rst = Nothing
    Set cnn = Nothing

Dim cnn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim cmd As ADODB.Command

' コネクションをセットし、Accessを開く

Set cnn = New ADODB.Connection

cnn.Provider = "Microsoft.Ace.OLEDB.12.0;"

' 任意のAccessファイルを設定する

cnn.Open ThisWorkbook.Path & "\ado3.accdb"

' コマンドをセット

Set cmd = New ADODB.Command

Set cmd.ActiveConnection = cnn

cmd.CommandText = "Q_data" ' クエリを設定

'レコードセットをセットし、パラメータを指定して実行

Set rst = New ADODB.Recordset

Set rst = cmd.Execute(Parameters:=Array("%札幌%", "%南%"))

If rst.EOF Then

MsgBox "データがありません。"

Else

'レコードをA1セルへ貼り付ける

Range("A1").CopyFromRecordset rst

End If

' 閉じる

rst.Close

cnn.Close

' 解放する

Set cmd = Nothing

Set rst = Nothing

Set cnn = Nothing

パラメータ付きを実行する場合は、commandオブジェクトを利用します。

パラメータ付きを実行するために、Accessのクエリを設定する

ExcelからADO(Microsoft ActiveX Data Objects)を利用してVBAを実行するために、Accessで複数パラメータを実行できるよう設定をする必要があります。

設定と言っても、任意の文字列で例えば「[para]」とか[[p1]]とかを設定するだけなので、簡単です。

ここでは、「[para1]」「[para2]」を設定していますが、パラメータの設定は「[para1]」「[para2]」と順にArrayで「”%札幌%”, “%南%”」と設定する必要があります。

「”%南%”,”%札幌%”」にするとデータが0件になります。