Transaction Fails

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

accessmonster3 - 29 Dec 2006 00:08 GMT

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

Reply to this Message

accessmonster3 - 29 Dec 2006 00:16 GMT

Access 2003 - MS SQL 2000 backend...

Reply to this Message

Dirk Goldgar - 29 Dec 2006 02:14 GMT

> 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
[quoted text clipped - 37 lines]
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message