Excel VBAでAccessのパラメータ付きアクションクエリを実行し、セルにコピーする
前回は、AccessのアクションクエリをExcel VBAを利用して実行する方法を説明しました。
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で利用できるようにすることです。 バインディングは2通りの方法があり、実行時に利用できるようにする実行時バインディング(遅延[…]
ExcelのVBAを利用して、Accessのアクションクエリをパラメータ付きで実行し、セルにコピーする基本的なコードは以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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]」に変更し、以下のソースで確認してみたが、データを取得することはできませんでした。
![「Like [para]」を「[para]」に変更](https://sunafukin.jp/wp-content/uploads/2021/06/ado2-2.png)
|
1 |
Set rst = cmd.Execute(Parameters:="Like ""%札幌%""") |
|
1 |
Set rst = cmd.Execute(Parameters:="Like '%札幌%'") |