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

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

VBA

1 Excel VBAでAccessのパラメータ付きアクションクエリを実行し、セルにコピーする
2 パラメータ付きを実行するために、Accessのクエリを設定する
3 VBAにLikeを含めたパラメータ付きで実行することはできなかった

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

前回は、AccessのアクションクエリをExcel VBAを利用して実行する方法を説明しました。

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

Excel VBAでAccessのアクションクエリを実行し、セルにコピーする自動化を行うにあたり、AccessのアクションクエリをExcel VBAを利用して実行する機会があったので調査しました。 VBAを実行する前に、「Micr[…]

前回に実行している途中、パラメータ付きだとどうやって実行するのか興味が出てきました。

今回は、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のアクションクエリをパラメータ付きで実行し、セルにコピーする基本的なコードは以下の通りです。

    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 & "\ado2.accdb"

    ' コマンドをセット
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    
    cmd.CommandText = "Q_data"  ' クエリを設定
    
    'レコードセットをセットし、パラメータを指定して実行
    Set rst = New ADODB.Recordset
    Set rst = cmd.Execute(Parameters:="%札幌%")
    
    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 & "\ado2.accdb"

' コマンドをセット

Set cmd = New ADODB.Command

Set cmd.ActiveConnection = cnn

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

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

Set rst = New ADODB.Recordset

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

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]]とかを設定するだけなので、簡単です。

ここでは、「[para]」を設定しています。

VBAにLikeを含めたパラメータ付きで実行することはできなかった

Accessより「Like [para]」を「[para]」に変更し、以下のソースで確認してみたが、データを取得することはできませんでした。

Set rst = cmd.Execute(Parameters:="Like ""%札幌%""")

1	Set rst = cmd.Execute(Parameters:="Like ""%札幌%""")

Set rst = cmd.Execute(Parameters:="Like '%札幌%'")

1	Set rst = cmd.Execute(Parameters:="Like '%札幌%'")