Solved

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

Posted on 2003-12-11
6
1,057 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 500 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

803 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