469,354 Members | 2,035 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,354 developers. It's quick & easy.

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

TheSmileyCoder
2,321 Expert Mod 2GB
I have been playing around with transactions and one thing got me wondering. First is the code I am using:

Expand|Select|Wrap|Line Numbers
  1. 'Start transaction
  2.         'Get default Workspace.
  3.             Dim wrkDefault As Workspace
  4.             Set wrkDefault = DBEngine.Workspaces(0)
  5.  
  6.         'Start of transaction
  7.             wrkDefault.BeginTrans
  8.             Dim strSQL As String
  9.             strSQL = "UPDATE tbl_Trans SET tx_Field='jjj' WHERE ID=2"
  10.             'DoCmd.RunSQL strSQL
  11.             CurrentDb.Execute strSQL
  12.             If vbYes = MsgBox("Commit changes?", vbYesNo) Then
  13.                 wrkDefault.CommitTrans
  14.                 Else
  15.                 wrkDefault.Rollback
  16.             End If
Now If I use CurrentDB.Execute 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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL strSQL,True
  2. DoCmd.RunSQL strSQL,False
With the same results.

Can anyone explain this, or point out something I am missing?
Apr 26 '11 #1
4 9202
ADezii
8,800 Expert 8TB
Not sure about the RunSQL approach, but I rarely see it used in the context of a Transaction. The following approach should always work:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim wrkDefault As DAO.Workspace
  3. Set wrkDefault = DBEngine.Workspaces(0)
  4.  
  5. wrkDefault.BeginTrans
  6.  
  7. strSQL = "UPDATE [Order Details] SET [UnitPrice] = [UnitPrice] * 1.1"
  8.  
  9. CurrentDb.Execute strSQL, dbFailOnError
  10.  
  11. If vbYes = MsgBox("Commit changes?", vbYesNo) Then
  12.   wrkDefault.CommitTrans
  13. Else
  14.   wrkDefault.Rollback
  15. End If
Apr 26 '11 #2
TheSmileyCoder
2,321 Expert Mod 2GB
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.Execute 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.Execute.
Apr 26 '11 #3
ADezii
8,800 Expert 8TB
From the little research that I have done, it appears that CurrentDB.Execute is significantly faster than DoCmd.RunSQL since it is a Method of the Jet Database Engine, and runs the SQL directly.
Apr 27 '11 #4
NeoPa
32,184 Expert Mod 16PB
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 :-)
Apr 27 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Jim | last post: by
4 posts views Thread by Rotsj | last post: by
reply views Thread by Andy | last post: by
3 posts views Thread by Pathfinder | last post: by
7 posts views Thread by Jimmer | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.