BeginTrans, CommitTrans and RollBack

[Tony Wainwright]

Hey guys

A quick question regarding transactions. I am writing a application and to
streamline the the code I am breaking it in to seperate routines. What I
want to do is:

Public Sub InsertData()
On Error GoTo InsertDataErr

DoCmd.SetWarnings False
BeginTrans
vProc = True
Call Insert1stSetOfData
Call Insert2ndSetOfData
......
vProc = False
CommitTrans
InsertDataRes:
Exit Sub
InsertDataErr:
If vProc = True Then Rollback
......
End Sub

Public Sub Insert1stSetOfData()
BeginTrans
vSQL = "INSERT INTO tblName (FieldList) " _
& "VALUES (" & value1 & ", '" & value2 & "');"
DoCmd.RunSQL vSQL
CommitTrans
End Sub


Public Sub Insert2ndSetOfData()
BeginTrans
vSQL = "INSERT INTO tblName (FieldList) " _
& "VALUES (" & value1 & ", '" & value2 & "');"
DoCmd.RunSQL vSQL
CommitTrans
End Sub

I have read that CommitTrans is global and that you can nest tranactions.
If there is an error in either Insert2ndSetOfData will the data in
Insert1stSetOfData?

 

[Allen Browne]

In your code, the main proc InsertData() has error handling. The sub procs
do not. If an error occurs in the sub procs, they will therefore pass
control back to the error handler of the main proc. At this point the main
proc performs a single rollback. If one of the sub procs has succeeded in
starting a 2nd transaction, then the 2nd one will be rolled back by the
error handler of the main proc, but the original proc remains in force.

Your code may therefore exit leaving the original transaction open (i.e. not
committed, and not rolled back). The approach is therefore seriously flawed.

To fix the problem, add error handling to both the sub procedures, with
their own commit and rollback. Remember that the secondary transaction is
still within the original transaction, and so the whole thing can still be
rolled back by the original transaction.

More info on safe coding of transactions:
http://allenbrowne.com/ser-37.html