Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2003-12-11
6
Medium Priority
?
1,091 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:PaulBarbin
  • 3
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
stevbe earned 2000 total points
ID: 9923637
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
 
LVL 9

Author Comment

by:PaulBarbin
ID: 9924155
Thanks Steve, worked like a champ.  I didn't want to jump into DAO, but its not as scary as I thought.

Paul
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9929826
you are welcome, I have not done transactions with ADO yet but I am sure it is possible. Thanks for the points.

Steve
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 39

Expert Comment

by:stevbe
ID: 9929892
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
 
LVL 9

Author Comment

by:PaulBarbin
ID: 9930231
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9935855
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question