【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する

★気に入ったらシェアをお願いします!


edge-of-a-cliff

photo credit: gagilas meditation via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。

前回はこちらの記事。

エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。今回はエクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度についてお伝えします。

エクセルVBAでAccessデータベースに複数のレコードを追加する方法についてお伝えしました。

SQLのINSERT文よりも、ADODBレコードセットオブジェクトのAddNewメソッド&Updateメソッドを使うほうが高速だということがわかりました。

さて、実務上はデータベースにレコードを蓄積する際に、誤って重複するレコードを追加してしまうということが起こり得るかも知れません。

そういう場合は、既にAccessデータベースに該当のデータが存在していないか確認をしてからの追加をするという仕組みがあると安心ですよね。

ということで、今回はエクセルVBAでAccessデータベースに特定のデータが存在するかどうかを判定するプログラムを作ってみたいと思います。

どうぞよろしくお願いいたします!

スポンサーリンク

サンプルプログラム

今回サンプルとするプログラムを紹介します。前回のプログラムを少し改良しました。

こちらです。

サンプルのテーブルとフィールドについて

エクセルファイルのSheet2にレコード追加したいデータが格納されていて、Accessデータベースのテーブル「都道府県」にすべてのデータについてレコード追加します。

フィールドは

|登録日|都道府県|推計人口|

の3つです。

登録日とレコード追加の運用イメージ

登録日については、InputBoxでユーザーに入力してもらう形になっていまして、年月まで採用して日は「1」に揃えるというルールにしています。

例えば

  • 2016年9月の各都道府県の推計人口
  • 2016年10月の各都道府県の推計人口

というように、各月ごとに集計完了したらデータベースにアップしていくという運用を想定しています。

ここで毎月の運用の際に、うっかり2回レコード追加をして、テーブルにデータが重複して格納されないように防ぎたいというわけでございます。

テーブルに特定条件のレコードが存在しているか調べる

このケースを防ぐにはIf文を使って

  • テーブル「都道府県」に既に同じ年月のデータが存在しているならば
    • 「データが既に存在しています」とメッセージを表示
    • 処理終了
  • さもなくば
    • レコード追加

という条件分岐をさせれば良さそうです。

では、「テーブル「都道府県」に既に同じ年月のデータが存在しているか」をどのように判定すればよいでしょうか?

SQLのSELECT文で条件に該当するデータを抽出する

実はこの記事の内容が使えます。

【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
エクセルVBAでAccessデータベースを操作するシリーズ。今回はAccessデータベースのテーブルから色々なパターンでデータを抽出するSELECT、WHEREの使い方についてお伝えします。

SQLのSELECT文で該当の条件でデータを抽出してみればわかる、という寸法です。

つまりアルゴリズムとしては

  • データベースに対してSQLのSELECT文で、該当の条件でデータを抽出してみる
  • その結果として得られたレコードセットが空であれば
    • 該当のデータはテーブルに存在しない
  • 空でなければ
    • 該当のデータがテーブルに存在する

ということになります。

SQLのSELECT文で条件を付けて抽出する場合は、WHERE句を使うんでしたね。

SELECT フィールド名 FROM テーブル名 WHERE 条件文

今回、ユーザーに入力してもらう年月はdateYMという変数に入りますので、データベースに問い合わせるSQL文としては

となります。

SQLの場合、日付は「#」で囲います。忘れると痛い目みますので、バッチリ覚えておいてください。

これで抽出されたデータがレコードセットに抽出できます。

EOFプロパティとBOFプロパティ

では、そのレコードセットが空かどうかはどう調べればよいでしょうか?

このような場合は、ADODBレコードセットオブジェクトのEOFプロパティを使います。

EOFプロパティはカレントレコードの位置が最終レコードよりも後の枠外にあるかどうかを表すプロパティです。

ADODBレコードセットオブジェクト.EOF

同時にカレントレコードの位置が先頭レコードよりも前の枠外にあるかどうかを表すBOFプロパティというのもあります。

ADODBレコードセットオブジェクト.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文はこんな感じになりますね。

レコードが存在していればadoRs.EOFはFalseですから、条件式はNotでその裏返しでTrueになります。

If内にGoTo文などでプロシージャの最後のほうに飛ばしてあげれば、レコード追加の処理を飛ばすことができます。

テーブルに特定のデータが存在するかどうかを判定するVBAプログラム

以上を踏まえまして、プログラムを作ってみました。

こちらです。

23行目~32行目が条件のレコードを抽出するSQL文によるレコードセットの抽出と、それが空かどうかのチェックをしている箇所ですね。

データがもし既に存在している場合は、GoTo文でプロシージャの最後のほうに設置したLabel01というラベルに飛ばしています。

まとめ

エクセルVBAでAccessデータベースに特定のデータが存在するかどうかを判定するプログラムについてお伝えしてきました。

SQLのSELECT文で確認したいレコードセットを抽出してみる、そしてEOFプロパティでチェックするという流れです。

毎月のデータをcsvなどからまとめてデータベースに投入するような運用ならば、おそらく必須機能かと思います。

ぜひご活用くださいね!

次回は、Accessデータベースのレコードを更新する方法についてお伝えします。

エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はUPDATEのSQL文を使ってAccessデータベースのレコードを呼び出して上書き更新する方法です。

どうぞお楽しみに!

連載目次:エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。
  1. なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
  2. エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
  3. 【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
  4. 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
  5. 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
  6. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  7. エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
  8. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  9. 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
  10. エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
  11. エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
  12. エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
  13. エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
  14. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  15. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  16. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  17. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出