asked on

Rollback transaction

I am using VB 6.0 to access Microsoft Access 2003 database using DAO.

I want to insert record into 3 tables, if One table fails, roll back all the transaction, how to do this?

Below tb001,tb002, and tb003 is recordset, But doing like this throw me error.

On Error GoTo HandleErr:
tb001.AddNew
tb001!Field1 = txtProdName

tb002.AddNew
tb002!Field1 = INITIAL

tb003.AddNew
tb003!Field1 = "abc"

tb001.Update
tb002.Update
tb003.Update

HandleErr:
tb001.CancelUpdate
tb002.CancelUpdate
tb003.CancelUpdate

So, what is the correct and easy way to do that?
P/S: I am open to all kind of solution, including change to ADO, or using the feature in Microsoft Access 2003, or anything else. Thanks.

ASKER CERTIFIED SOLUTION

Dabas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

(Not sure if this is different within executing in VB and Access VBA, but...)

In DAO the BeginTrans, CommitTrans, and RollbackTrans methods belong to the Workspace object, which you will need to instantiate and use to perform these methods.

on error goto error_handler

Dim wks as DAO.Workspace, db as DAO.Database
Set wks = DBEngine.Workspaces(0)
Set db = wks.OpenDatabase("Path goes here")

wks.BeginTrnas

'Your code goes here

wks.CommitTrans

exit_function:
exit function

error_handler:
'Do your error handling stuff here
wks.RollbackTrans
goto exit_function

end function

Hope this helps.
-Jim

Dabas

Jim

I have used BeginTrans and related command without declaring a Workspace in all of my programs.
The example as presented by me will work.

Dabas

william007

ASKER

Thanks:-)