Question on Transactions for Allen Browne

[TonyT]

Hi Allen,

I was trying to troubleshoot an issue I was having with a Transaction, and
came accross your guide http://allenbrowne.com/ser-37.html

The problem I was having was an error 3034 when trying to Rollback a failed
transaction. The resolution to my problem was to change the order of my exit
routine to rollback the transaction before setting the db=Nothing code, but I
see your code has it as I originally did - ie. Set db=Nothing before the
Rollback.

This failed the rollback with the above error (3034).

It makes sense in hindsight that the database object should be open when the
execute statements are rolled back, but I've not had it fail before, and I'm
sure I've used it in that order myself previously.

So, my question is, is this a different approach required by 2007 (my
previous experience has all been with 2000), or are there other factors that
determine the order in which objects must be set to Nothing and Transaction
Processing?

 

[Allen Browne]

Did you BeginTrans on the WorkSpace (as in the example)?
Or did you BeginTrans on the Database?

 

[TonyT]

On the workspace.

extract of the code;

Dim db As DAO.Database
Dim wrkSpace As DAO.Workspace
Dim bTransComp As Boolean, bTransStart As Boolean

Set wrkSpace = DBEngine.Workspaces(0)
Set db = CurrentDb

wrkSpace.BeginTrans
bTransStart = True
bunch of update code
strS = "Update tblMake SET ModID = " & lngNewModID & " WHERE ModID = " &
lngOldModID & ";"
db.Execute strS, dbFailOnError
above code repeated for other related tables

wrkSpace.CommitTrans
bTransComp = True

other code in here Not in a Transaction

exit_Part:

If bTransStart And Not bTransComp Then
'transaction failed - rollback
wrkSpace.Rollback
End If
Set db = Nothing
Set rcd = Nothing
Set wrkSpace = Nothing

Exit Sub

err_Part:

MsgBox ("error: " & Err.Number & " unable to Update Makes.")

Resume exit_Part

End Sub
------

The above code works just fine, however;

Set db = Nothing
Set rcd = Nothing
If bTransStart And Not bTransComp Then
'transaction failed - rollback
wrkSpace.Rollback
End If
Set wrkSpace = Nothing

raises error 3034, no current workspace or transaction. Couldn't figure why
it was working like this, so just changed the order of the exit_Part routine
and now it works fine.

I didn't use your On Error Resume Next in the exit routine, so it was
endlessly looping the exit and error routines, but not rolling back either.

If it makes any odds, I triggered the failure in the update code using
Err.Raise 8888

TonyT..

 

[Allen Browne]

Try replacing:
Set db = CurrentDb
with:
Set db = wrkSpace(0)

 

[TonyT]

Sorry to bother you Allen,
I've tried re-creating the error here again and can't, so I must have
changed something else at the same time that cured the problem and not
remembered it afterwards. Damn lousy bug tester at times...

thanks anyway,

TonyT..