Rolling back transactions in Access (using ADO, DAO, Access code, whatever)
Posted on 2003-12-11
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.