photo credit: gagilas meditation via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。
前回はこちらの記事。
エクセルVBAでAccessデータベースに複数のレコードを追加する方法についてお伝えしました。
SQLのINSERT文よりも、ADODBレコードセットオブジェクトのAddNewメソッド＆Updateメソッドを使うほうが高速だということがわかりました。
さて、実務上はデータベースにレコードを蓄積する際に、誤って重複するレコードを追加してしまうということが起こり得るかも知れません。
そういう場合は、既にAccessデータベースに該当のデータが存在していないか確認をしてからの追加をするという仕組みがあると安心ですよね。
ということで、今回はエクセルVBAでAccessデータベースに特定のデータが存在するかどうかを判定するプログラムを作ってみたいと思います。
どうぞよろしくお願いいたします！
サンプルプログラム
今回サンプルとするプログラムを紹介します。前回のプログラムを少し改良しました。
こちらです。
Sub addRecord()
Dim strFileName As String
strFileName = "test3.accdb"
Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続
Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
'ワークシートとイテレータの準備
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
Dim i As Long
i = 2
'***** 年月を取得 *****
Dim dateYM As Date
dateYM = InputBox("年月を入力してください(YYYY/M)")
'***** データを登録 *****
With adoRs
.Open "都道府県", adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く
Do While ws.Cells(i, 1).Value <> ""
.AddNew
!登録日 = DateSerial(Year(dateYM), Month(dateYM), 1) '今月の1日の日付
!都道府県 = ws.Cells(i, 2).Value
!推計人口 = ws.Cells(i, 4).Value
.Update
i = i + 1
Loop
.Close 'レコードセットのクローズ
End With
adoCn.Close 'コネクションのクローズ
Set adoRs = Nothing
Set adoCn = Nothing 'オブジェクトの破棄
End Sub
サンプルのテーブルとフィールドについて
エクセルファイルのSheet2にレコード追加したいデータが格納されていて、Accessデータベースのテーブル「都道府県」にすべてのデータについてレコード追加します。
フィールドは
の３つです。
登録日とレコード追加の運用イメージ
登録日については、InputBoxでユーザーに入力してもらう形になっていまして、年月まで採用して日は「1」に揃えるというルールにしています。
例えば
- 2016年9月の各都道府県の推計人口
- 2016年10月の各都道府県の推計人口
- …
というように、各月ごとに集計完了したらデータベースにアップしていくという運用を想定しています。
ここで毎月の運用の際に、うっかり２回レコード追加をして、テーブルにデータが重複して格納されないように防ぎたいというわけでございます。
テーブルに特定条件のレコードが存在しているか調べる
このケースを防ぐにはIf文を使って
- テーブル「都道府県」に既に同じ年月のデータが存在しているならば
- 「データが既に存在しています」とメッセージを表示
- 処理終了
- さもなくば
- レコード追加
という条件分岐をさせれば良さそうです。
では、「テーブル「都道府県」に既に同じ年月のデータが存在しているか」をどのように判定すればよいでしょうか？
SQLのSELECT文で条件に該当するデータを抽出する
実はこの記事の内容が使えます。
SQLのSELECT文で該当の条件でデータを抽出してみればわかる、という寸法です。
つまりアルゴリズムとしては
- データベースに対してSQLのSELECT文で、該当の条件でデータを抽出してみる
- その結果として得られたレコードセットが空であれば
- 該当のデータはテーブルに存在しない
- 空でなければ
- 該当のデータがテーブルに存在する
ということになります。
SQLのSELECT文で条件を付けて抽出する場合は、WHERE句を使うんでしたね。
今回、ユーザーに入力してもらう年月はdateYMという変数に入りますので、データベースに問い合わせるSQL文としては
strSQL = "SELECT * FROM 都道府県 WHERE 登録日=#" & dateYM & "#"
となります。
SQLの場合、日付は「#」で囲います。忘れると痛い目みますので、バッチリ覚えておいてください。
これで抽出されたデータがレコードセットに抽出できます。
EOFプロパティとBOFプロパティ
では、そのレコードセットが空かどうかはどう調べればよいでしょうか？
このような場合は、ADODBレコードセットオブジェクトのEOFプロパティを使います。
EOFプロパティはカレントレコードの位置が最終レコードよりも後の枠外にあるかどうかを表すプロパティです。
同時にカレントレコードの位置が先頭レコードよりも前の枠外にあるかどうかを表すBOFプロパティというのもあります。
なんやそれ？
という感じですよね。一つ一つ説明しますね。
EOFプロパティでレコードセットが空かどうかを調べる
まず、レコードセット内で参照できるレコードは1つだけで、その参照対象となっているレコードをカレントレコードと言います。
ADODBレコードセットオブジェクトが新たに生成された直後は、先頭レコ―ドがカレントレコードになっています。
それぞれBOFはBegin Of File、EOFはEnd Of Fileの略で、それぞれカレントレコードが先頭より前または最終より後にあるときにTrueとなります。
| カレントレコードの位置 | BOF | EOF |
|---|---|---|
| レコード外（BOF） | True | Flase |
| 先頭レコード | False | False |
| … | False | False |
| 最終レコード | False | False |
| レコード外（EOF） | False | True |
何らかのレコードが存在していれば、レコードセットが生成された瞬間は先頭レコードがカレントレコードになるので、その時点ではBOFもEOFもFalseとなります。
ところが、レコードが一つも存在していない場合はどうなるでしょうか。
| カレントレコードの位置 | BOF | EOF |
|---|---|---|
| レコード外 | True | True |
となりまして、レコードが一つもない場合はBOFもEOFもTrueとなります。
ですから、EOFの値を見れば（BOFでも良いです）、レコードセットが空かどうかが判定できるということになります。
If文はこんな感じになりますね。
If Not adoRs.EOF Then '抽出したRSにデータが存在しているかどうかをチェック
'レコードが存在している場合の処理
End If
レコードが存在していればadoRs.EOFはFalseですから、条件式はNotでその裏返しでTrueになります。
If内にGoTo文などでプロシージャの最後のほうに飛ばしてあげれば、レコード追加の処理を飛ばすことができます。
テーブルに特定のデータが存在するかどうかを判定するVBAプログラム
以上を踏まえまして、プログラムを作ってみました。
こちらです。
Sub addRecords()
Dim strFileName As String
strFileName = "test3.accdb"
Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続
Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
'ワークシートとイテレータの準備
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
Dim i As Long, j As Long
'***** 年月を取得＆年月重複チェック *****
Dim dateYM As Date
dateYM = InputBox("年月を入力してください(YYYY/M)")
Dim strSQL As String
'入力した年月のデータを抽出するSQL
strSQL = "SELECT * FROM 都道府県 WHERE 登録日=#" & dateYM & "#"
adoRs.Open strSQL, adoCn
If Not adoRs.EOF Then '抽出したRSにデータが存在しているかどうかをチェック
MsgBox "既に" & Year(dateYM) & "/" & Month(dateYM) & "のデータがテーブルに存在しています", vbInformation
GoTo Label01
End If
adoRs.Close
'***** データを登録 *****
With adoRs
.Open "都道府県", adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く
i = 2
Do While ws.Cells(i, 1).Value <> ""
.AddNew
!登録日 = DateSerial(Year(dateYM), Month(dateYM), 1) '今月の1日の日付
!都道府県 = ws.Cells(i, 2).Value
!推計人口 = ws.Cells(i, 4).Value
.Update
i = i + 1
Loop
End With
Label01:
'***** データベース終了処理 *****
adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ
Set adoRs = Nothing
Set adoCn = Nothing 'オブジェクトの破棄
End Sub
23行目～32行目が条件のレコードを抽出するSQL文によるレコードセットの抽出と、それが空かどうかのチェックをしている箇所ですね。
データがもし既に存在している場合は、GoTo文でプロシージャの最後のほうに設置したLabel01というラベルに飛ばしています。
まとめ
エクセルVBAでAccessデータベースに特定のデータが存在するかどうかを判定するプログラムについてお伝えしてきました。
SQLのSELECT文で確認したいレコードセットを抽出してみる、そしてEOFプロパティでチェックするという流れです。
毎月のデータをcsvなどからまとめてデータベースに投入するような運用ならば、おそらく必須機能かと思います。
ぜひご活用くださいね！
次回は、Accessデータベースのレコードを更新する方法についてお伝えします。
どうぞお楽しみに！
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する
エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。- なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
- エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
- 【エクセルVBA＆Access連携】SQL文でデータを抽出する最も簡単なプログラム
- 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
- 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
- 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
- エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
- 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
- エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
- エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
- エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
- エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
- エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
- エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
- エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
- エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる
