Transaction Fails

[accessmonster3 via AccessMonster.com]

I enjoy the ability proposed with transactions but it seems they actually
cause my vba to FAIL. This code works great IF I don't have the transaction
code, adding the begintrans etc cause it to fail on the SECOND customer with
odd errors like no current record or can't commit record without begin
transaction.

Sample Code Snippet

Set rstInvoice = CurrentDb.OpenRecordset("tbl_Invoice", dbOpenDynaset,
dbSeeChanges)

Set ws = DBEngine(0)
do while not rstCustomers.eof
ws.BeginTrans
InvoiceSub = 0
rstInvoice.AddNew
rstInvoice!InvoiceDate = Date
rstInvoice.Update

rstInvoice.Bookmark = rstInvoice.LastModified (**Qeustion 1: is this
really necessary? **)
newInvoiceID = rstInvoice!InvoiceID 'to easily reference what invoice id
we are working on

then a ton of code generating each line entry
InvoiceSub = InvoiceSub + eachline

rstInvoice.Edit (**Question 2: it fails here on the SECOND customer with
'no current record', why would it work the first time? it ONLY fails if the
transactions are being used otherwise everything works great**)
rstInvoice!Subtotal = InvoiceSub
rstInvoice.Update

If TransError then
ws.rollback
else
ws.committrans
end if

rstCustomers.MoveNext
Loop

 

[accessmonster3 via AccessMonster.com]

Access 2003 - MS SQL 2000 backend...

 

[Dirk Goldgar]

I enjoy the ability proposed with transactions but it seems they
actually cause my vba to FAIL. This code works great IF I don't have
the transaction code, adding the begintrans etc cause it to fail on
the SECOND customer with odd errors like no current record or can't
commit record without begin transaction.

Sample Code Snippet

Set rstInvoice = CurrentDb.OpenRecordset("tbl_Invoice",
dbOpenDynaset, dbSeeChanges)

Set ws = DBEngine(0)
do while not rstCustomers.eof
ws.BeginTrans
InvoiceSub = 0
rstInvoice.AddNew
rstInvoice!InvoiceDate = Date
rstInvoice.Update

rstInvoice.Bookmark = rstInvoice.LastModified (**Qeustion 1: is
this really necessary? **)
newInvoiceID = rstInvoice!InvoiceID 'to easily reference what
invoice id we are working on

then a ton of code generating each line entry
InvoiceSub = InvoiceSub + eachline

rstInvoice.Edit (**Question 2: it fails here on the SECOND
customer with 'no current record', why would it work the first time?
it ONLY fails if the transactions are being used otherwise everything
works great**) rstInvoice!Subtotal = InvoiceSub
rstInvoice.Update

If TransError then
ws.rollback
else
ws.committrans
end if

rstCustomers.MoveNext
Loop

I'm not sure what's going on. I'm suspicious of the fact that you're
opening your recordset rstInvoice outside the transaction and updating
it inside the transaction. I don't know whether that's supposed to work
or not. You might try (a) deriving your recordset from a Database
object derives from the same workspace object you're using, and (b)
opening the rstInvoice recordset inside the transaction. Like this:

'----- start of code snippet -----
Dim db As DAO.Database

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)

Do Until rstCustomers.EOF

ws.BeginTrans

Set rstInvoice = db.OpenRecordset( _
"SELECT * FROM tbl_Invoice WHERE 1 = 0", _
dbOpenDynaset, dbSeeChanges)

InvoiceSub = 0

rstInvoice.AddNew
rstInvoice!InvoiceDate = Date
rstInvoice.Update

rstInvoice.Bookmark = rstInvoice.LastModified
' (**Question 1: is this really necessary? **)
' (Answer: yes)

newInvoiceID = rstInvoice!InvoiceID
'to easily reference what invoice id we are working on

'... then a ton of code generating each line entry ...
' ... InvoiceSub = InvoiceSub + eachline ...

rstInvoice.Edit
rstInvoice!Subtotal = InvoiceSub
rstInvoice.Update

rstInvoice.Close
Set rstInvoice = Nothing

If TransError Then
ws.Rollback
Else
ws.CommitTrans
End If

rstCustomers.MoveNext

Loop
'----- end of code snippet -----

Now, I don't know that this will fix your problem. I'm just thinking
that would clear up some areas that I'm wondering about.