Excel VBAでAccessのアクションクエリをSQLで実行し、セルにコピー後はデータを削除する
前回は、AccessでアクションクエリにSQLを利用してデータを取得し、セルにコピー後は取得済フラグを取得済みをExcel VBA実行する方法を説明しました。
Excel VBAでAccessのアクションクエリをSQLで実行し、セルにコピー後は取得済フラグを取得済みに設定する 前回は、AccessでアクションクエリにSQLを利用してExcel VBA実行する方法を説明しました。 [site[…]
しかし、上記の方法だと、データが増え続けるため、いつかはAccessファイルの容量上限に達してしまううかもしれません。
そこで、今回は、セルにコピーしたデータを削除して、データが増えないようにします。
VBAを実行する前に、「Microsoft ActiveX Data Objects 6.1 Libraly」または「Microsoft ActiveX Data Objects 2.X Libraly」を参照設定します。
参照設定については、以下で説明しています。
事前バインディングと実行時バインディングの違い VBAのバインディングとは、外部のオブジェクトの機能をVBAで利用できるようにすることです。 バインディングは2通りの方法があり、実行時に利用できるようにする実行時バインディング(遅延[…]
ExcelのVBAで、AccessのアクションクエリをSQLで実行し、セルにコピーした後、SQLで削除する基本的なコードは以下のような感じになります。
ポイントは以下のとおりです。
- 15行目でSELECTという命令に取得済フラグを利用してデータを取得するSQL文を作成する。
- 17行目でSQLを実行することでデータを取得する。
- 29行目でコピーしたデータは削除するSQL文を作成する。
- 31行目でSQLを実行することでデータを削除する。
また、23～25行で、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 36 |
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String Dim i As Long ' コネクションをセットし、Accessを開く Set cnn = New ADODB.Connection cnn.Provider = "Microsoft.Ace.OLEDB.12.0;" cnn.Open ThisWorkbook.Path & "\ado7.accdb" 'レコードセットをセット Set rst = New ADODB.Recordset ' ' アクションクエリのSQL文を作成する strSQL = "select * from Q_data where 市区町村名 Like '%札幌%' and 町域名 Like '%南%'" ' AccessのアクションクエリをSQLで実行して開く rst.Open strSQL, cnn If rst.EOF Then MsgBox "データがありません。", vbInformation Else ' ヘッダーを貼り付けする For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1).Value = rst.Fields(i).Name Next i 'レコードをA1セルへ貼り付ける Range("A2").CopyFromRecordset rst ' コピーしたデータは削除する strSQL = "delete from Q_data where 市区町村名 Like '%札幌%' and 町域名 Like '%南%';" cnn.Execute (strSQL) End If ' 閉じる rst.Close cnn.Close |
SQLでデータを取得をする場合は、Accessのアクションクエリの設定はなし
ExcelからADO(Microsoft ActiveX Data Objects)を利用してSQLを実行する場合、Accessのクエリの抽出条件で設定するものはありません。
しかし、Accessは、データを削除してもデータのゴミが残り、ファイルのサイズが増え続けますので、定期的に最適化をする必要があります。
機会があれば、VBAでAccessを最適化する方法をやりたいと思います。
今回はDELETEを利用して実行する方法を説明しました。SQLに慣れると、SQLだけでデータを自由自在に扱えるようになりますので、SQLをどんどん使っていきましょう。
また、SQLはデータ分析や整形などにも活用できます。