AccessVBAコミットとロールバックの位置

いつもお世話になっております。

BeginTrans、CommitTrans、RollbackTransの記述位置について
お聞きしたい事があります。

合計で2のテーブルにインサート処理を行います。

Private Sub cmd_Click()
Dim strSQL AS String

'(1)adoコネクション生成
Set dbCon = Application.CurrentProject.Connection
strSQL = "テーブルAに挿入するInsert文"

'(2)テーブルaにデータ挿入
dbCon.Execute (strSQL)

'(3)テーブルaのデータをレコードセットへ
strSQL = "SELECT * FROM テーブルA"
Set dbRes = dbCon.Execute(strSQL)

Do Until dbRes.EOF
====================================================
ここでdbResの値を使用してテーブルbへ挿入するSQL文を作成
====================================================
Loop
'(4)テーブルbにデータ挿入
dbCon.Execute (strSQL)

以上の場合、テーブルaへのInsert文をCommitTransしなければ
テーブルbで使用するためデータを作成できません。

(4)でエラーが発生するとテーブルaは更新確定されているので戻り事ができないのですが
どのようにCommitTransを使用すればよろしいでしょうか？？

テーブルaの挿入が失敗した場合は処理を抜けて
テーブルbの挿入が失敗した場合はテーブルaを元の状態（RollbackTrans）したいです。

ご教授お願い致しますm（ _ _）m

No.7 ベストアンサー 回答者： 30246kiku 回答日時： 2011/09/28 18:52

＃６です

BeginTrans 等、あまりネストして使わないので間違っているかもです。

> コネクションをネストする知識が無いもので・・・
これは、コネクションのネストではなく、トランザクションのネストになると思います。

まずは、
> 多分、コネクション１つの方がネストの説明が楽だと思うので

の説明からしてみたいと思います。

以下のように１つのコネクションを２つの変数に設定した時、
各変数を用いてトランザクションを開始すると、
どの変数で開始しようとも、元のコネクションでのネストとして扱われるみたいです。

　　Dim cn1 As ADODB.Connection
　　Dim cn2 As ADODB.Connection

　　Set cn1 = CurrentProject.Connection
　　Set cn2 = CurrentProject.Connection

　　Debug.Print cn1.BeginTrans() ' 1 が表示
　　Debug.Print cn2.BeginTrans() ' 2 が表示

※ cn2.BeginTrans() で、1 にならない

これを踏まえて、以下の処理を見てみると

　　Set cn1 = CurrentProject.Connection
　　Set cn2 = CurrentProject.Connection

　　For i = 1 To x
　　　　cn1.BeginTrans ' ★1
　　　　cn2.BeginTrans ' ★2
　　　　For j = 1 To y
　　　　　　cn2.BeginTrans ' ★3
　　　　　　　　cn2.Execute "・・・・・・・"
　　　　　　cn2.CommitTrans '☆3
　　　　Next

　　　　cn1.Execute "・・・・・・・"
　　　　cn1.CommitTrans ' ☆2
　　　　cn2.CommitTrans ' ☆1
　　Next

※ ★/☆ はネストのレベル位置を示してみましたが、わかりにくいでしょうか。

この処理では、実際にはネスト２、３部分を使って処理されているように見えます。
ネスト１段削除して、For 文も削除して、変数を１つにしてみると

　　cn.BeginTrans ' ★1
　　　　cn.BeginTrans ' ★2
　　　　　　cn.Execute "・・・・・・・"
　　　　cn.CommitTrans '☆2

　　　　cn.Execute "・・・・・・・"
　　cn.CommitTrans ' ☆1

＃６と同じ構成になりますね。


> cn.Execute "DELETE * FROM TA;"
> cn.CommitTrans: iLv = iLv - 1
> rs.Open
> DELETE * FROM TAをcn.CommitTransしているのに rs.Openできてしまうのは何故？

CommitTrans はチョッと置いておいて、

１）
cn.Execute "DELETE * FROM TA;"
rs.Open "TA", cn, adOpenForwardOnly, adLockPessimistic

は、普通にできるものと思っています。
同じコネクションを使って記述しているので、前の処理が終わらないうちに・・・・
っていう状況はないものと思っています。

DoCmd.RunSQL って使ったことありませんが、

DoCmd.RunSQL "DELETE * FROM TA;"
rs.Open "TA", cn, adOpenForwardOnly, adLockPessimistic

てなことがあったら、
DoCmd.RunSQL は、どのルートでテーブルを触りに行くのかわからないし、
いろいろと非同期という言葉も目にします。

同じコネクションを使って記述した上記 １） なら、順に処理されるものと思います。

CommitTrans はトランザクションを確定するものと思っています。
確定した後は何でもできるような気がします。

で、例を考えた時、INSERT 文を書くにはいろいろと前提条件が必要だし、
レコードをいじる・・・DELETE で良いかな・・・
当初考えたのは、

　　cn.BeginTrans
　　　　cn.Execute "DELETE * FROM TA;"
　　　　rs.Open "TA", cn, adOpenForwardOnly, adLockPessimistic
　　　　If (rs.EOF) Then
　　　　　　Debug.Print "New"
　　　　　　rs.AddNew
　　　　　　rs(1) = "ZZZZ"
　　　　　　rs.Update
　　　　End If
　　　　rs.Close
　　cn.RollbackTrans

でしたが、

> 以上の場合、テーブルaへのInsert文をCommitTransしなければ
> テーブルbで使用するためデータを作成できません。

という言葉もあって、それならって、
INSERT を DELETE に変更した手前、DELETE 部分を挟んでみました。
（INSERT と DELETE では、動きが異なるのかもしれません：この辺わかりません）

　　　cn.Execute "INSERT INTO TA ・・・・・・;"
　　　rs.Open "TA", cn, adOpenForwardOnly, adLockPessimistic
ってなことをやる時ありますが、おかしな動きを感じたことは今までなかったです。
（私が経験していないだけかもしれません）

また、レコードセットを得る時
> Set dbRes = dbCon.Execute(strSQL)
という記述は、ほとんどやったことが無いので rs.Open との違いはわかりません。

私がいじってみていたのは、Access2007 accdb 単体になります。

この回答へのお礼

丁寧な回答、本当にありがとうございます。

感覚的にはＩＦ文の中にＩＦ文がある感覚でしょうか。

理解ができました、少し複雑かつ正確なプログラムが構築できそうです。

お礼日時：2011/09/30 09:07

No.6 回答者： 30246kiku 回答日時： 2011/09/27 18:51

多分、コネクション１つの方がネストの説明が楽だと思うので

（私が理解している範囲になります：間違っているかも・・・・）

テスト用の環境でテーブル「TA」を作って、レコードを何件か入れておきます。
また、テーブルデザインで上から２つ目はテキスト型としておきます。

以下サンプルは、テーブル「TA」をクリアして、
テーブルが空だったらデータ１件追加します。
（この時イミディエイトウィンドウに New が表示されるはずです）

で、最後で Rollback します。

テーブル「TA」を開いてみると、元々のデータがそのままあると思います。

Public Sub Sample()
　　Dim cn As ADODB.Connection
　　Dim rs As New ADODB.Recordset

　　Set cn = CurrentProject.Connection

　　cn.BeginTrans
　　　　cn.BeginTrans ' ★
　　　　　　cn.Execute "DELETE * FROM TA;"
　　　　cn.CommitTrans ' ★
　　　　rs.Open "TA", cn, adOpenForwardOnly, adLockPessimistic
　　　　If (rs.EOF) Then
　　　　　　Debug.Print "New"
　　　　　　rs.AddNew
　　　　　　rs(1) = "ZZZZ"
　　　　　　rs.Update
　　　　End If
　　　　rs.Close
　　cn.RollbackTrans
　　Set cn = Nothing
End Sub


※ 最後の cn.RollbackTrans を cn.CommitTrans に変更すると、
　　追加した１件だけになっていると思います。（私の2007では）

※※ 上記★部分をコメントにしても、New は表示されましたけど・・・・
　　データ量によるものでしょうか・・・（詳しくはわかりません）

なので
> 以上の場合、テーブルaへのInsert文をCommitTransしなければ
> テーブルbで使用するためデータを作成できません。
の動作については確認してません。


バフッとしたエラー処理）

BeginTrans の戻り値は、１スタートでネスト位置を教えてくれるようです。

　　iLv = cn.BeginTrans()
　　　　iLv = cn.BeginTrans()

とすると、１回目は iLv = 1 に、２回目は iLv = 2 に
そこで、CommitTrans　/　RollbackTrans　時に iLv = iLv - 1 してあげれば
ネスト位置がわかると思います。

途中エラーがあったら、iLv = 0 になるまで　RollbackTrans　するとか・・・


Public Sub Sample()
　　Dim cn As ADODB.Connection
　　Dim rs As New ADODB.Recordset
　　Dim iLv As Long

　　iLv = 0
　　Set cn = CurrentProject.Connection

　　On Error GoTo ERR_HND
　　iLv = cn.BeginTrans()
　　　　iLv = cn.BeginTrans()
　　　　　　cn.Execute "DELETE * FROM TA;"
　　　　cn.CommitTrans: iLv = iLv - 1
　　　　rs.Open "TA", cn, adOpenForwardOnly, adLockPessimistic
　　　　If (rs.EOF) Then
　　　　　　Debug.Print "New"
　　　　　　rs.AddNew
　　　　　　rs(1) = "ZZZZ"
　　　　　　rs.Update
　　　　End If
　　　　rs.Close
　　cn.RollbackTrans: iLv = iLv - 1
ERR_EXIT:
　　Set cn = Nothing
　　Exit Sub

ERR_HND:
　　While (iLv > 0)
　　　　cn.RollbackTrans: iLv = iLv - 1
　　Wend
　　Resume ERR_EXIT
End Sub

この回答へのお礼

ご返答ありがとうございます。
コネクションをネストする知識が無いもので・・・

とても基本的な話で申し訳ないのですが

　　cn.Execute "DELETE * FROM TA;"
　　　　cn.CommitTrans: iLv = iLv - 1
　　　　rs.Open
DELETE * FROM TAをcn.CommitTransしているのに
　rs.Openできてしまうのは何故でしょう・・・？

お礼日時：2011/09/28 11:51

No.5 回答者： piroin654 回答日時： 2011/09/27 10:04

以下のような感じでは？

Set dbCon1 = Application.CurrentProject.Connection
Set dbCon2 = Application.CurrentProject.Connection

On Error Goto ER_Shori_1
dbCon1.BeginTrance
'(2)テーブルaにデータ挿入
'テーブルaの挿入が失敗した場合は処理を抜けて
dbCon1.Execute (strSQL)
dbCon1.ComitTrance
On Error Goto 0


On Error Goto ER_Shori_2
dbCon2.BeginTrance
Do Until dbRes.EOF
====================================================
ここでdbResの値を使用してテーブルbへ挿入するSQL文を作成
====================================================
Loop
'(4)テーブルbにデータ挿入
dbCon2.Execute (strSQL)
dbCon2.ComitTrance

dbCon_Exit:
Set dbCon1 = Nothing
Set dbCon2 = Nothing
ExitSub

ER_Shori_1:
dbCon1.Rollback
Resume dbCon_Exit

ER_Shori_2:
dbCon2.Rollback
Resume dbCon_Exit

この回答へのお礼

回答ありがとうございます。

ER_Shori_2でdbCon2.Rollbackすると
(2)テーブルaにデータ挿入は既にコミットされているように見えるのですが

テーブルaは元の状態にロールバックしてくれるのでしょうか？？

お礼日時：2011/09/28 10:22

No.2 回答者： nicotinism 回答日時： 2011/09/26 21:28

トランザクション処理を入れ子（ネスト）にしたら？

http://www.accessclub.jp/bbs6/0020/das6365.html

この回答へのお礼

ご返答ありがとうございます。

Connectionを2つ作成して入れ子にするやり方でしぃうか。

知らない技術でした、勉強になりました。

ありがとうございます。

お礼日時：2011/09/28 10:15

No.1 回答者： piroin654 回答日時： 2011/09/26 19:10

状況によりますが、一般的には、

On Error Goto ER
　・　
　・
dbCon.BeginTrance
dbCon.Execute (strSQL)
　・
　・
　・
(4)テーブルbにデータ挿入
dbCon.Execute (strSQL)
dbCon.ComitTrance
　・
　・
ER:
dbCon.Rollback


のような感じですが。