VBA- SQL Server ODBC in transaction

[Kalor]

Hi all,

I have an mdb (UserMgr) with linked tables to both an another Access MDB and a SQL Server db. My code adds records to these tables within a transaction.

Trouble is, it's ignoring the transaction!

My code goes like this (dbCurr is UserMgr) :
------------------snippety snip--------------
BeginTrans
set qryUser = dbCurr.CreateQuerydef("","<insert a record into table A>")
qryUser.execute
If qryUser.recordsaffected <> 1 then
Msgbox <yadda yadda>
Rollback
end if

set qryUser = dbCurr.CreateQuerydef("","<insert a record into table B>")
qryUser.execute
If qryUser.recordsaffected <> 1 then
Msgbox <yadda yadda>
Rollback
end if
.
.
.
CommitTrans
------------------snippety snip--------------
Any ideas? I don't seem to be able to use dbCurr.<Begintrans/Committrans/Rollback>. Do I have to declare a seperate db variable for the access and SQL Server databases?

advaTHANKSnce,

Kalor.

 

[Kalor]

Ah, workspace object. Been away from Access too long.

Nothing to see here. Move along