VBAコードをガシガシ書いてSQLコードもどんどんかけるような方には関係ない話ですが、私の様な初中級者には、
さらに詳しく
クエリと言う便利なAccessのツールを何段にも実行して値を得なければならない状況
があります。
しかし、トランザクション処理ができないと何かと不便なので、複数クエリを実行しても何かエラーがでたらロールバックして何も無かった状態まで戻せるようなVBAコードを見つけて使っていました。
目次
クエリ複数実行トランザクション処理できてる？
ある時から、エラーが出たときに、データの相違がでたことがあり、そこから原因を追究していたのですが、トランザクション処理が効いているならデータの相違はないはずなのです。
更に深く探ってみると、その処理をしているVBA内を見ると、複数のクエリをトランザクション内で動かしているのです。
実験をしてみて、
メモ
本当に処理ができているのか？できてない場合はどうしたらいいのか？
を検証してみたいと思います。
検証するために、テストデータベースを準備
検証するためにテストデータベースを準備しました。
参考
- buyテーブル・buyフォーム・・・・・購入数を帳票フォームから入力させる用途。
- jobテーブル・・・・・buyテーブルから一時処理テーブルと想定してコピーしjobテーブルで処理
- stockテーブル・・・・・在庫テーブル、jobテーブルの購入数を在庫テーブルから減算する。
VBAの実行としては、buyフォームに後ほど、実行ボタンを設置して、下記の処理をクエリでさせる予定です。
- jobテーブル内データを削除（リセット）
- buyテーブルデータ内容を、jobテーブルに追加
- jobテーブル内の購入数をstockテーブルの在庫数から減算
VBA内で実行するクエリの準備
３つの処理をVBA内で実行させるための、クエリを準備しました。
- job_delete・・・・・jobテーブル内データをすべて削除するクエリ
- buy_add・・・・・jobテーブルにbuyテーブルデータを追加（購入データの一時処理テーブル準備）
- stock_edit・・・・・jobテーブルの購入数を元に、stokテーブルの在庫数を減算処理
実行するVBAコードを準備
Private Sub stock_btn_Click()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
If MsgBox("処理を実行しますか？ ", vbYesNo, "更新確認") = vbYes Then
Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
On Error GoTo ErrorHandler
cmd.CommandText = "BEGIN TRANSACTION"
cmd.Execute
DoCmd.SetWarnings False
DoCmd.OpenQuery "job_delete" 'jobテーブル内データリセット
DoCmd.OpenQuery "buy_add" 'jobテーブルにデータ追加
DoCmd.OpenQuery "stock_edit" 'stockテーブルの在庫数減算処理
cmd.CommandText = "COMMIT TRANSACTION"
cmd.Execute
Set cmd = Nothing
cn.Close: Set cn = Nothing
Else
MsgBox "処理せずに終了しました。"
Exit Sub
End If
ExitErrorHandler:
Exit Sub
ErrorHandler:
cmd.CommandText = "ROLLBACK TRANSACTION"
cmd.Execute
Set cmd = Nothing
cn.Close: Set cn = Nothing
MsgBox "エラーが発生しました。処理せずに戻ります。"
End
End Sub
実験その１
最初実行前に、
ココがポイント
jobテーブル内にはダミーデータ
を入れておきます。
実行すると、３段のクエリが実行される訳ですが、最初のクエリである、
ココがポイント
jobテーブルのダミーデータの存在で、ロールバックしているかどうかを検証
してみたいと思います。
まず、あえて、２段目のbuy_addのつづりを間違えて実行してみたいと思います。
当然エラーになりますが、トランザクション処理で何事も無かったようになっていれば、jobテーブル内のダミーデータが削除されずに残っているはずです。結果は、
ココに注意
第１段のクエリだけ実行されて、ロールバックしていませんでした( ﾉД`)ｼｸｼｸ…
つまり、jobテーブル内のデータは消されて無くなっていましたので、ロールバックされていません。
このやり方での、VBA内でクエリを実行してもトランザクション処理はできないってことが実証されました。
今後、この方法は使えないですね。
実験その２
ネット検索していて、この方法なら行けるってのがあったので、やってみました。
結果は、同じですね。失敗です。
データは削除されて、ロールバックしていませんでした。
実験その３
ココがポイント
VBA内に直接SQLを埋め込む方法
でやったら、１段目のクエリの削除がロールバックされて戻ったので、２段目のクエリも、わざと３段目のクエリにエラー文字を書き込んで実行したところ、ロールバックが確認できました。
実験その３でうまく行ったように感じたのは、第１段のSQLの部分で記入ミスがあり、まったく実験の意味が無く、デバッグを続ける事３時間で記入ミスに気づきました。
job_deleteじゃなく、jobというテーブルを指定しなければならない所を、クエリを指定してエラーにさせていました。
こんな凡ミスで時間を費やしてしまうのが開発です。
複数クエリをトランザクションする方法（結論）
複数クエリをそのままトランザクション付きVBAに記入しても、先走りしているクエリはロールバックしない事がわかりました。
つまり、複数クエリをそのままVBA内に記入してはダメってことであり、
ココがポイント
クエリをＳＱＬ文に変えてＶＢＡ内で動かせばロールバックする
ことが確認できました。
ちなみに、私はＳＱＬ文を書くことが本当に苦手でしたが、Accessで作成したクエリをＳＱＬモードにして、それをただ単にコピーしても使えることが分かりました。
これなら、ＳＱＬ文も抵抗がなく、ＶＢＡに組み入れることも今後増えていくかもしれません。
下記に実験に成功したＶＢＡを記載しておきます。
Private Sub stock_btn_Click()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim SQL As String
If MsgBox("処理を実行しますか？ ", vbYesNo, "更新確認") = vbYes Then
Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
On Error GoTo ErrorHandler
cn.BeginTrans
SQL = "DELETE * FROM job" 'jobテーブル内データリセット
cmd.CommandText = SQL
cmd.Execute
SQL = "INSERT INTO job " 'jobテーブルにデータ追加
SQL = SQL & "SELECT * FROM buy"
cmd.CommandText = SQL
cmd.Execute
SQL = "UPDATE " 'jobテーブルにデータ追加"
SQL = SQL & "stock INNER JOIN job "
SQL = SQL & "ON stock.ID = job.ID "
SQL = SQL & "SET stock.在庫数 = [stock]![在庫数]-[job]![購入数]"
' SQL = "UPDATE stock INNER JOIN job ON stock.ID = job.ID SET stock.在庫数 = [stock]![在庫数]-[job]![購入数]" '一行の場合
cmd.CommandText = SQL
cmd.Execute
cn.CommitTrans
Set cmd = Nothing
cn.Close: Set cn = Nothing
Else
MsgBox "処理せずに終了しました。"
Exit Sub
End If
ExitErrorHandler:
Exit Sub
ErrorHandler:
cn.RollbackTrans
Set cmd = Nothing
cn.Close: Set cn = Nothing
MsgBox "エラーが発生しました。処理せずに戻ります。"
End
End Sub