Excel VBAでAccessのアクションクエリをSQLで実行し、セルにコピー後は取得済フラグを取得済みに設定する
前回は、AccessでアクションクエリにSQLを利用してExcel VBA実行する方法を説明しました。
Excel VBAでAccessのアクションクエリをSQLで実行し、セルにコピーする 前回は、AccessでアクションクエリにFilterを利用してExcel VBA実行する方法を説明しました。 [sitecard subtitle[…]
今回は、さらに取得済フラグを設定することで、取得済みのデータは2度と取得しないようにします。
2度と取得しない設定にするためには、Accessのフィールド名に取得済フラグを追加し、データを取得したあとSQLのUPDATE(更新)命令を使って取得済フラグで取得済みに設定します。
その後、再びデータを取得する際に、取得済フラグを確認し、未取得であればデータを取得します。
以上の流れを作成してみます。
VBAを実行する前に、「Microsoft ActiveX Data Objects 6.1 Libraly」または「Microsoft ActiveX Data Objects 2.X Libraly」を参照設定します。
参照設定については、以下で説明しています。
事前バインディングと実行時バインディングの違い VBAのバインディングとは、外部のオブジェクトの機能をVBAで利用できるようにすることです。 バインディングは2通りの方法があり、実行時に利用できるようにする実行時バインディング(遅延[…]
ExcelのVBAで、AccessのアクションクエリをSQLで実行し、セルにコピーした後、コピー済フラグにコピー済として1と設定する基本的なコードは以下のような感じになります。
ポイントは以下のとおりです。
- 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 & "\ado6.accdb" 'レコードセットをセット Set rst = New ADODB.Recordset ' アクションクエリのSQL文を作成する strSQL = "select * from Q_data where 市区町村名 Like '%札幌%' and 町域名 Like '%南%' and 取得済フラグ = 0" ' 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 = "update Q_data set 取得済フラグ = 1 where 市区町村名 Like '%札幌%' and 町域名 Like '%南%'" cnn.Execute (strSQL) End If ' 閉じる rst.Close cnn.Close |
SQLでデータを取得をする場合は、Accessのアクションクエリの設定はなし
ExcelからADO(Microsoft ActiveX Data Objects)を利用してSQLを実行する場合、Accessのクエリの抽出条件で設定するものはありません。
今回はUPDATEを利用して実行する方法を説明しました。SQLはとても便利ですので、SQLをどんどん使っていきましょう。
次回は、データを取得したら、AccessからDELETEを利用して取得したデータを削除する方法を説明します。