Excel VBAでAccessのアクションクエリを実行し、セルにコピーする
自動化を行うにあたり、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 |
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset ' コネクションをセットし、Accessを開く Set cnn = New ADODB.Connection cnn.Provider = "Microsoft.Ace.OLEDB.12.0;" cnn.Open ThisWorkbook.Path & "\ado1.accdb" 'レコードセットをセット Set rst = New ADODB.Recordset 'Accessのアクションクエリを開く rst.Open "Q_data", cnn If rst.EOF Then MsgBox "データがありません。", vbInformation Else 'レコードをA1セルへ貼り付ける Range("A1").CopyFromRecordset rst End If ' 閉じる rst.Close cnn.Close ' 解放する Set rst = Nothing Set cnn = Nothing |
ここで大きな落とし穴があります。
Accessのアクションクエリ実行は、アスタリスク(*)のワイルドカード文字を使う
Accessでアクションクエリを実行する場合、LIKEのワイルドカードはアスタリスク(*)です。ここは、抽出条件で「LIKE “*札幌*”」と設定します。
しかし、アスタリスクのままVBAからアクションクエリを実行すると0件になってしまいます。
ExcelからADO利用してアクションクエリ実行は、パーセント(%)のワイルドカード文字を使う
ExcelからADO(Microsoft ActiveX Data Objects)を利用してVBAを実行する場合、Likeのワイルドカードはパーセント(%)です。SQLと同じですね。
ここはで、「LIKE “%札幌%”」と設定します。
ワイルドカード文字のまとめ
まとめると、Accessのアクションクエリの抽出条件で札幌という文字を抽出する場合、以下のように設定します。
| 用途 | Accessでアクションクエリ実行 | VBAでADOを利用してアクションクエリ実行 |
| 説明 | ワイルドカードはアスタリスク(*) | ワイルドカードはパーセント(%) |
| 利用例 | LIKE “*札幌*” | LIKE “%札幌%” |
VBAでADOを利用してデータを抽出する際、エラーが起きていないが、データが0件になっていたら、ワイルドカードも疑ったほうが良いかもしれません。
VBAでSQLを扱うときは、気をつけましょう。