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.
LVL 9
william007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DabasCommented:
Hi william007,
On Error GoTo HandleErr:

BeginTrans
tb001.AddNew
tb001!Field1 = txtProdName

tb002.AddNew
tb002!Field1 = INITIAL

tb003.AddNew
tb003!Field1 = "abc"

tb001.Update
tb002.Update
tb003.Update
CommitTrans

HandleErr:
RollBack

I suggest you read the help for BeginTrans to get better undrestanding on how Transactions work

Dabas

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornSQL Server Data DudeCommented:
(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
DabasCommented:
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
william007Author Commented:
Thanks:-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.