Access 2000 SQL server Transactions

[cascot]

I'm using Access 2000 with SQL Server 2000.

When I run a piece of code (only the transaction part of it is included below) the code appears to complete successfully, but certain tables then appear to be locked and any calls to them result in an "ODBC--Call Failed" type error.

If I simply comment out the wspTrans.BeginTrans and wspTrans.CommitTrans, everything works perfectly.

Can anyone suggest what I may be doing wrong in the following code...(I have inserted some comments [**Comments**] where I think they may be useful)

Dim wspTrans As Workspace
Dim qd As QueryDef

Set wspTrans = DBEngine.Workspaces(0)

db.Execute "DELETE FROM HuskyIn_A", dbFailOnError + dbSeeChanges

Set qd = db.QueryDefs("aHuskyIn_OrderCancelA")
[** aHuskyIn_OrderCancel copies data to Husky_A table **]
qd.Parameters!prmOrderHeaderId = tmpOrderHeaderId

On Error GoTo err_pbContinue_Click_Rollback
wspTrans.BeginTrans

qd.Execute dbFailOnError + dbSeeChanges
Set qd = db.QueryDefs("aHuskyIn_OrderCancelB")
[** aHuskyIn_OrderCancelB makes use of the data just copied to Husky_A to create records in HuskyIn **]
qd.Execute dbFailOnError + dbSeeChanges

strSQL = "DELETE FROM OrderDetail " & _
" WHERE OrderHeaderId = " & CStr(tmpOrderHeaderId) & _
" AND [Finished] = 0"
db.Execute strSQL, dbFailOnError + dbSeeChanges

wspTrans.CommitTrans

When I get to a stage where I can see the table data, the records have NOT been deleted from OrderDetail, though the records are present in both Husky_A and HuskyIn.

 

[cmmrfrds]

I am not sure how DAO works with linked sql server tables. An alternative is to use a stored procedure on the sql server side and perform the transaction through sql server. Another option is to use ADO. Maybe somebody else will comment on the DAO transaction.