Transactions failing in Loop

[cboyda via AccessMonster.com]

I admit it.. I don't quite fully understand how these transactions work, so
sure it works on iteration but fail for i+1.

This code works.. but then if any ONE item fails in the do until loop then
ALL transactions are rolled back.. I want to iterate through and if one fails
JUST that one is rolledback.

Code that works:
Set ws = DBEngine(0)
Set db = ws(0)
ws.BeginTrans
bInTrans = True
Do Until rstCustomers2Invoice.EOF
'Transaction init
'remember any sql statements called must include failonerror
' db.Execute strSql, dbFailOnError
Debug.Print
"**************************************************************************************"
CountCustomers = CountCustomers + 1
Debug.Print "Working on Customer Number " & CountCustomers & " of " &
MaxCustomers & " " & rstCustomers2Invoice!FirstName
RetVal = SysCmd(2, CountCustomers)
'start invoice
Set rstInvoice = db.OpenRecordset("tbl_invoice")
rstInvoice.AddNew

Code that I want.. that fails:
Set ws = DBEngine(0)
Set db = ws(0)
Do Until rstCustomers2Invoice.EOF
ws.BeginTrans
bInTrans = True
'Transaction init
'remember any sql statements called must include failonerror
' db.Execute strSql, dbFailOnError
Debug.Print
"**************************************************************************************"
CountCustomers = CountCustomers + 1
Debug.Print "Working on Customer Number " & CountCustomers & " of " &
MaxCustomers & " " & rstCustomers2Invoice!FirstName
RetVal = SysCmd(2, CountCustomers)
'start invoice
Set rstInvoice = db.OpenRecordset("tbl_invoice")
rstInvoice.AddNew

This fails on the rstInvoice.Addnew for the second iteration through the loop.
.. probably because db.openrecordset is now a second database connection.

ERROR 3420
Object Invalid or no longer set.

How can I fix this to get a transaction set for each iteration instead of the
whole group?

 

[Guest]

Hi

Try:

1. Get rid of the first BeginTrans in line 3 - this may be overiding the
equivalent line in the loop.
2. Make sure you have the CommitTrans statement at the back end of the loop.
3. Include Error Capture within the loop to deal with RollBack.

e.g
On error GoTo Err_Trans
Do Until rstCustomers2Invoice.EOF
ws.BeginTrans
....coding
ws.CommitTrans
GoTo NoProbs
Err_Trans:
ws.RollBack
NoProbs:
rstCustomers2Invoice.MoveNext
Loop

Cheers.

BW

 

[Guest]

Hi, me again.

Looking at your coding I can't see what your rstInvoice.addnew is doing?
Unless there is coding that you haven't included you are adding a new record
but not allocating any data to it, and then not .Update 'ing it to save the
record?

Also, as you have already 'Set rstInvoice = db.OpenRecordset("tbl_invoice")'
early in the procedure, you do not need to do it again within the loop.

Cheers. BW