Rollback in error handler

[robert d via AccessMonster.com]

If I'm using BeginTrans and CommitTrans with a DAO Workspace, do I have to
include Rollback in my error handler if an error occurs after the BeginTrans
statement but before the CommitTrans statement can be executed. I wouldn't
think so, but I'm not sure.

 

[Guest]

You sure do. You routine must look something similar to the following:

On Error GoTo my_Err
Dim inTrans As Boolean
....
BeginTrans
inTrans = True
....
CommitTrans
inTrans = False
....
my_Exit:
Exit Sub ' or Function
my_Err:
If inTrans Then RollBack
' handle error, like MsgBox Err.Description or logError into a file, etc.
Resume my_Exit
Exit Sub ' (or Function)

If you do not Rollback the transaction then all the tables that participated
in the transaction will remain to be locked (until explicit Rollback or
Commit statement is executed or you close the MSAccess, which will trigger an
implicit rollback
)

HTH

 

[TC]

Sergey has it absolutely spot-on. Including not setting the flag until
immediately /after/ entering transaction state, and resetting the flag
immediately after exitting transaction state.

It would be nice for the error handler to just say:

on error resume next
ws.rollback

but from memory, rollback causes an untrappable error if you do it
without any pending transaction.

Warning: I seem to remember that rollback can affect all recordsets
that are urrently open in the relevant workspace. You may need to
re-open an/or re-position them. Check to see if this happens in your
situation.

HTH,
TC [MVP Access]