Transactions on linked tables

[foreveryoung]

Can someone advise me on transactions in the following scenario.

Linked tables to sql server
The present code uses the syntax docmd.openquery .....

How do I set up transactions for the above. There is presently 30 lines of code each with a docmd.openquery command on it. Because lots of these are action queries I want to use transactions.

Thanks
David

 

[lameid]

First thing to know is that Access has multiple object models and only a few of them support transactions...

Docmd.openquery does not.

Instead begin a transaction and then use the execute method of a querydef object. I haven't dealt much with ADO yet but with DAO...

dim ws as workspace
dim db as database
dim qry as querydef

set db = currentdb
set ws = db.parent 'workspace of currently open
'database or you could create a new one

WS.begintrans
set qry = db.querydefs("query name&quot
qry.execute

set qry = db.querydefs("query name2&quot
qry.execute

ws.committrans


That is DAO. ADO works similarly but uses the connection object for transactions instead of a workspace...