エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる


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

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

前回の記事はコチラ。

エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。SQL文にFormat関数を使って特定の日付のレコードを抽出する方法。また応用で特定の年月や曜日でグルーピングもできます。

Accessデータベースへ問い合わせをするSQL文にFormat関数を使う方法についてお伝えしました。

さて、今回ですが、ちょっと方向を変えましてトランザクションについてお伝えしたいと思います。

データベース処理の途中でエラー等の問題が起きたとき…どうなっちゃうか心配ですよね。そんな、心配を払拭してくれるテクニックです。

では、エクセルVBAでAccessデータベースを扱う際にトランザクション処理を行う方法、行ってみましょう。

スポンサーリンク

トランザクション処理とは何か

例えば、Accessデータベースに対してレコードの追加や上書きをする大量の処理があるとします。

上手な組み方を考える必要があるのですが、処理をするレコード数が多いのであれば、場合によっては数十秒とか数分とかかかってしまうときがあります。

エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
エクセルVBAでAccessデータベースを操作する方法について、今回はADODBレコードセットのFind、Update、MoveFirstを使って更新する方法について、またその実行速度についてです。

そんな処理の途中で、例えば

  • エラーが発生した
  • 電源が落ちた
  • LAN線が抜けた

などなどの不可抗力が発生し、完了できずに中断してしまうとします。

そうすると、作業を再開しようと思っても「あれ?どこまで追加(または上書き)したっけ?」ということで、どこまで処理が完了したのかを調査して、その状態に応じて対策を講じなければいけません。これは大変そうです…。

そのような状況を防ぐのが「トランザクション処理」というわけです。

トランザクション処理とは、データベースを利用するシステムにおいて、処理の一貫性を保証するために、関連する一連の処理全体を一個の処理単位として管理する仕組みのことである。

引用:IT用語辞典BINARY

ちょっと言葉が難しいですが、もうちょっとわかりやすく言うと、データベースに対する一連の処理を「全部成功」か「全部失敗」のどちらかを保証するための仕組みです。

どっかで失敗したら「全部失敗」ということで、最初からなかったことにします。「全部成功」したときだけ、全部の処理を確定します。

転ばぬ先の杖的な仕組みですね。

データベースのトランザクション処理に使用する3つのメソッド

エクセルVBAでAccessデータベースに対してトランザクション処理を行う際には、ADODBコネクションオブジェクトの3つのメソッドを使います。

BeginTransでトランザクションを開始する

まず、BeginTransメソッドでトランザクションを開始します。

ADODBコネクションオブジェクト.BeginTrans

このメソッド以降で実行されたデータベースのへの操作がトランザクション処理の対象となり、後述するCommitTransまたはRollbackTransメソッドを呼び出してトランザクションを終了するまで、変更は加えられない状態となります。

CommitTransメソッドで変更を確定する

トランザクション対象となる処理で全て問題なかった場合には、CommitTransメソッドで変更を確定します。

ADODBコネクションオブジェクト.CommitTrans

CommitTransメソッドを呼び出すとトランザクション処理は終了します。

RollbackTransメソッドで変更をキャンセルする

RollbackTransメソッドは、データベースへの全ての変更をキャンセルしてトランザクション処理を終了します。

ADODBコネクションオブジェクト.RollbackTrans

このメソッドは一般的に、On Error Gotoを使ってエラーをキャッチして飛んだ先の処理として入れていきます。

トランザクション処理を伴うデータベース操作をするプログラム

Accessデータベース操作にトランザクション処理を含めると、以下のようなプログラムになります。

Sub transDB()

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb;" 'Accessファイルに接続

On Error Goto Catch 'エラーが起きたらCatchへ
adoCn.BeginTrans 'トランザクション開始

’データベースへの何らかの操作をする

adoCn.CommitTrans '確定処理をしてトランザクション終了
adoCn.Close: Set adoCn = Nothing 'コネクションのクローズと破棄
Exit Sub

Catch:
adoCn.RollbackTrans 'キャンセル処理をしてトランザクション終了
adoCn.Close: Set adoCn = Nothing 'コネクションのクローズと破棄

End Sub

まとめ

エクセルVBAでAccessデータベースを操作する際にトランザクション処理を入れる方法についてお伝えしました。

データベースの操作をしている途中で異常停止してしまうと、本当にわけがわからなくなります。

ですから、トランザクション処理…ものすごい助かります。転ばぬ先の杖として、ぜひご活用下さい。

さて、長いことエクセルVBAによる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のデータをORDER BYで並び替えをして取り出す
  13. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  14. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  15. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  16. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
  17. エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる

タイトルとURLをコピーしました