DAO transactions with parameter queries

[josephwalter]

There is no transaction control on my Macros (they execute a half dozen queries at a time), so I want to convert them to VB procedures. I've done a lot of VB coding before, but I'm having a bit of trouble with this one...

I would like to use ADO programming techniques, but I'm having problems opening a connection to the db while I've got the mdb open; therefore, I've resorted to using DAO programming techniques.

I understand that I need a workspace object, and that I need to use BeginTrans, CommitTrans, and Rollback to manage my transaction. What's the best method to execute my queries, though? ...Keeping in mind that I have parameter queries that prompt the user for values, as well as queries that reference values on the forms.

 

[vbajock]

Opening an ADO connection to an open database is easy:

Dim conn as new ADODB.Connection

set conn=CurrentProject.connection

Once the connection is open, nest your transactions between

conn.begintrans

conn.committrans

You can even nest docmd.openquery "somequery" calls and the processor will treat those the same as recordset transactions.

 

[josephwalter]

That's the route I want to take. Thank you!

But how do I execute the Queries in the database? I found an AllQueries property in the CurrentData object, but I couldn't figure out how to tell it to Execute.

 

[vbajock]

conn.begintrans

Docmd.openquery "SomeQuery"
Docmd.openquery "AnotherQuery"
etc
conn.committrans

is pretty simplistic. If they reference values on forms, the forms must be open when the query executes. On your paramtized queries, they'll still pop up and ask the same old questions.

 

[josephwalter]

I changed the conn.CommitTrans line to conn.RollbackTrans, and ran the procedure. The changes made by the query (executed with the Docmd.OpenQuery method) were not rolled back. Is there a way to run the queries that will let me rollback the changes if/when something goes wrong?