【Access】複数クエリをVBAでトランザクションをしてもロールバックしない問題を検証

VBAコードをガシガシ書いてSQLコードもどんどんかけるような方には関係ない話ですが、私の様な初中級者には、

があります。

しかし、トランザクション処理ができないと何かと不便なので、複数クエリを実行しても何かエラーがでたらロールバックして何も無かった状態まで戻せるようなVBAコードを見つけて使っていました。

クエリ複数実行トランザクション処理できてる？

ある時から、エラーが出たときに、データの相違がでたことがあり、そこから原因を追究していたのですが、トランザクション処理が効いているならデータの相違はないはずなのです。

更に深く探ってみると、その処理をしているVBA内を見ると、複数のクエリをトランザクション内で動かしているのです。

実験をしてみて、

を検証してみたいと思います。

検証するために、テストデータベースを準備

検証するためにテストデータベースを準備しました。

VBAの実行としては、buyフォームに後ほど、実行ボタンを設置して、下記の処理をクエリでさせる予定です。

1. jobテーブル内データを削除（リセット）
2. buyテーブルデータ内容を、jobテーブルに追加
3. jobテーブル内の購入数をstockテーブルの在庫数から減算

VBA内で実行するクエリの準備

３つの処理をVBA内で実行させるための、クエリを準備しました。

1. job_delete・・・・・jobテーブル内データをすべて削除するクエリ
2. buy_add・・・・・jobテーブルにbuyテーブルデータを追加（購入データの一時処理テーブル準備）
3. 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

実験その１

最初実行前に、

を入れておきます。

実行すると、３段のクエリが実行される訳ですが、最初のクエリである、

してみたいと思います。

まず、あえて、２段目のbuy_addのつづりを間違えて実行してみたいと思います。

当然エラーになりますが、トランザクション処理で何事も無かったようになっていれば、jobテーブル内のダミーデータが削除されずに残っているはずです。結果は、

つまり、jobテーブル内のデータは消されて無くなっていましたので、ロールバックされていません。

このやり方での、VBA内でクエリを実行してもトランザクション処理はできないってことが実証されました。

今後、この方法は使えないですね。

実験その２

ネット検索していて、この方法なら行けるってのがあったので、やってみました。

結果は、同じですね。失敗です。

データは削除されて、ロールバックしていませんでした。

実験その３

でやったら、１段目のクエリの削除がロールバックされて戻ったので、２段目のクエリも、わざと３段目のクエリにエラー文字を書き込んで実行したところ、ロールバックが確認できました。

実験その３でうまく行ったように感じたのは、第１段の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