Running Query classes inside transactions

[Jonathan Scott via AccessMonster.com]

Is it not possible in Access97? I've tried the following ways to execute
queries:

docmd.openquery thisquery
dbengine(0)(0).execute thisquery
dbengine(0)(0).execute thisquery.name
dbengine(0)(0).execute thisquery.sql
currentdb.execute <same as the three above>
thisquery.execute

The only one that successfully executes without complaining is the first. But
this one ignores my rollback. Do I have to spell out my queries as strings to
be able to execute them inside of a transaction?

Also, I am curious, does Access 2003 allow DDL inside of transactions? I find
it difficult to work with Access97 because it does not allow DDL inside of
transactions.

TIA,
Jonathan Scott

 

[Brendan Reynolds]

I'm assuming that 'thisquery' is a DAO QueryDef?

I don't have Access 97 installed, but I am reasonably certain that this
code, which I tested under Access 2003, would behave identically under
Access 97. I tested in a modified copy of Northwind, from which I'd removed
the relationship between Customers and Orders (otherwise you'd get an RI
error when you attempted to delete the Customer record) ...

Public Sub TestSub2()

Dim wsp As DAO.Workspace
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans
Set db = wsp.Databases(0)
On Error Resume Next
db.QueryDefs.Delete "TestQuery"
On Error GoTo 0
Set qdf = db.CreateQueryDef("TestQuery", "DELETE * FROM Customers WHERE
CustomerID = 'ALFKI'")
qdf.Execute dbFailOnError
Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount FROM Customers
WHERE CustomerID = 'ALFKI'")
Debug.Print "Within transaction ..."
Debug.Print rst.Fields("TheCount")
rst.Close
wsp.Rollback
Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount FROM Customers
WHERE CustomerID = 'ALFKI'")
Debug.Print "After rollback ..."
Debug.Print rst.Fields("TheCount")
rst.Close

End Sub

Result in the Immediate window ...

testsub2
Within transaction ...
0
After rollback ...
1

 

[Jonathan Scott via AccessMonster.com]

Brendan,

Thanks for the code snippet. It demonstrated where I was going wrong in my
thinking. I thought that when I get my querydefs and such from the same place,
it must be the same connection. Chances are I was being given difference
connections and thus was not seeing my rollback behave correctly.

BTW, I found that I can in fact issue DDL inside of a transaction in Access97.
Perhaps the book I was reading is older than Access97...

Thanks again,
Jonathan Scott