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

photo credit: sandklef It’s a deal – here’s the money via photopin (license)

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

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

前回の記事はコチラ。

エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出

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

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

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

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

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

トランザクション処理とは何か
データベースのトランザクション処理に使用する３つのメソッド
トランザクション処理を伴うデータベース操作をするプログラム
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

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

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

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

エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について

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

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

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

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

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

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

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

引用：IT用語辞典BINARY

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

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

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

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

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

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