Rolling back transactions in Access (using ADO, DAO, Access code, whatever)

I'm a SQL programmer, fairly new to Access...

I have an Access database that I created a form in, within the form, I want to run several action queries (to insert data, update data, and delete other data) over a series of 4 queries.  If any of them fail, I need to roll them all back.  I thought the ability to rollback would be inherent to Access 2000 but apparently not.

I tried to use ADO since I am familiar with that, but two problems:
1. ADO asks for the path of the file - that is really redundant since I am IN the database that I want to interact with, but I provided it the path anyway.
2. When I try to open the database that I am ALREADY IN,  it tells me that (80004005) "database has been placed in a state that prevents it from being open or locked"

Should I be using DAO?  Is there a better way to connect using ADO?  Can I use functions and calls inherent to VBA or A2000 that will allow rollbacks.

Thanks for your time.

Paul

LVL 9
PaulBarbinAsked:
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.

stevbeCommented:
DAO makes it very simple ...

Public Function TRANS()

Dim blnTrans As Boolean
Dim ws As DAO.Workspace

On Error GoTo ERR_TRANS

Set ws = DBEngine(0)

ws.BeginTrans
    blnTrans = True
    DBEngine(0)(0).Execute ...blah your SQL, dbFailOnError
    DBEngine(0)(0).Execute ...blah your SQL, dbFailOnError
    DBEngine(0)(0).Execute ...blah your SQL, dbFailOnError
ws.Committrans

EXIT_TRANS:
   Set ws = Nothing
    Exit Function
ERR_TRANS
   If blnTrans = True Then
      ws.Rollback
  End If
  Resume EXIT_TRANS
End Function

Steve
0

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
PaulBarbinAuthor Commented:
Thanks Steve, worked like a champ.  I didn't want to jump into DAO, but its not as scary as I thought.

Paul
0
stevbeCommented:
you are welcome, I have not done transactions with ADO yet but I am sure it is possible. Thanks for the points.

Steve
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

stevbeCommented:
I crack me up ... here is a link to a question I answered a while ago on how to do transactions with ADO...

http://www.experts-exchange.com/Databases/MS_Access/Q_20734189.html#9928326

Steve
0
PaulBarbinAuthor Commented:
I had seen a link with that and tried it "currentproject.connection", but it didn't work - I probably typo'd it.  I am much more familiar with ADO and would have preferred that route.  Thanks again for the help.

Paul
0
Alan WarrenApplications DeveloperCommented:
Hi Steve,

Wondering if you might look into an excel question for me,
Re: Sharing conflicts with excel linked tables.
The guy is losing heart, so I told him I would try to find the excel dude (you) LOL

http://www.experts-exchange.com/Databases/MS_Access/Q_20824776.html

Sorry for spamming your post Paul.

Alan :)
0
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
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.