Solved

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

Posted on 2003-12-11
6
1,066 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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 specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

830 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