Whats is the Transactional difference between Docmd.RunSQL and CurrentDb.Execu te SQL?

[TheSmileyCoder]

I have been playing around with transactions and one thing got me wondering. First is the code I am using:

'Start transaction
        'Get default Workspace.
            Dim wrkDefault As Workspace
            Set wrkDefault = DBEngine.Workspaces(0)
 
        'Start of transaction
            wrkDefault.BeginTrans
            Dim strSQL As String
            strSQL = "UPDATE tbl_Trans SET tx_Field='jjj' WHERE ID=2"
            'DoCmd.RunSQL strSQL
            CurrentDb.Execute strSQL
            If vbYes = MsgBox("Commit changes?", vbYesNo) Then
                wrkDefault.CommitTrans
                Else
                wrkDefault.Rollback
            End If

Now If I use CurrentDB.Execu te strSQL then it works as expected and rolls back the changes I made.
However, If I use DoCmd.RunSQL strSQL the rollback does not take effect.

Now I have also tried both:

DoCmd.RunSQL strSQL,True
DoCmd.RunSQL strSQL,False

With the same results.

Can anyone explain this, or point out something I am missing?

[ADezii]

Not sure about the RunSQL approach, but I rarely see it used in the context of a Transaction. The following approach should always work:

Dim strSQL As String
Dim wrkDefault As DAO.Workspace
Set wrkDefault = DBEngine.Workspaces(0)
 
wrkDefault.BeginTrans
 
strSQL = "UPDATE [Order Details] SET [UnitPrice] = [UnitPrice] * 1.1"
 
CurrentDb.Execute strSQL, dbFailOnError
 
If vbYes = MsgBox("Commit changes?", vbYesNo) Then
  wrkDefault.CommitTrans
Else
  wrkDefault.Rollback
End If

[TheSmileyCoder]

Hi Adezii and thank you for your prompt reply. I did read it earlier today, but was hoping someone else had more to offer. As I said in the post, I did get the transaction to work using currentDB.Execu te strSQL. I guess for a long time I have just been used to using Docmd.RunSQL, so I was curious as why I couldn't get that to work, since it actually specifically takes an argument to indicate whether or not it should be considered part of an transaction.

If I get the time, I will try to research if there is a performance difference between Docmd.RunSQL and CurrentDB.Execu te.

[ADezii]

From the little research that I have done, it appears that CurrentDB.Execu te is significantly faster than DoCmd.RunSQL since it is a Method of the Jet Database Engine, and runs the SQL directly.

[NeoPa]

I haven't got much to add I'm afraid Smiley, but I did read the RunSQL action topic in the Help system which included :
Select Yes to include this query in a transaction. Select No if you don't want to use a transaction. The default is Yes. Prior to Access 97, Access always included the query in a transaction when you ran this action by starting with a BeginTrans method, executing the SQL statement, and then concluding with a CommitTrans method. If you select No for this argument, the query may run faster.

From this I conclude that it probably aught to work exactly as you have used it, but for some reason doesn't :-S

As for which works faster, I would expect the requirement to handle transactions to make a significant difference (whichever method is employed), but I'd be surprised if the two methods differed noticeably in performance outside of that. I say this in the full confidence that my expectations are of little importance, as you will determine the truth of the matter with your testing very shortly :-)