Transactions

[Forest Dragon]

Hello,

I have an Access action query which I am trying to execute from VB6 by using ADO. This query is just one of many queries that I need to execute together by using a transaction.

The problem is that after I run the program and commit the transaction, it doesn't seem the query has been executed at all, whereas the other queries in the transaction have been executed (it's an "INSERT INTO" query and no record is added to the table). I think it's related somehow to the fact that in order to execute this query, I use the Execute method of the Command object (because this is a parameterized query), whereas in order to execute the other queries, I use the Execute method of the Connection object.

Anyway, here's my code (ConnClass.ADOConnJet returns the Connection object):

Do While Not ErrorOccured And Counter <= UBound(MesimotChanged)
'Build Command
Set Comm = New Command
With Comm
.CommandType = adCmdText
Set .ActiveConnection = ConnClass.ADOConnJet
ConnClass.ADOConnJet.BeginTrans
'Execute Queries
.
.
.
If Not ErrorOccured Then
'Execute the problematic query
With Comm
.CommandText = InsertMesimaSQL
.Parameters.Refresh
.Execute
End With
End If
If Not ErrorOccured Then
'Commit the Transaction
ConnClass.ADOConnJet.CommitTrans
Counter = Counter + 1
Else: ConnClass.ADOConnJet.RollbackTrans
End If
Set Comm = Nothing
Loop

Does anyone have an idea what can be wrong?

Thanks!

i had a similar problem once and figured out that Access's action queries run differently under ADO and DAO.

let's say we have an 'append query' and one of the records cannot be added due to key violations.
DAO: all records are added exept the one, that violates the rules
ADO: no record will be added at all

maybe that is your problem,

hope that helps

Sascha