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.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: - DoCmd.RunSQL strSQL,True
-
DoCmd.RunSQL strSQL,False
With the same results. Can anyone explain this, or point out something I am missing? 4 9880
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
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.
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.
NeoPa 32,556
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 :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jim |
last post by:
I am using Access 97 on a PC running Windows NT 4.0 SP6a.
I have some code (shown below) intended to add a set of records to one
table (tblGradeProps) when a new record is created in another...
|
by: Rotsj |
last post by:
Hi,
i try to execute an update query from within a form, but i get the message:
run time error '3144'. Syntax error on the update statement
when i try something like this:
DoCmd.RunSQL...
|
by: Andy |
last post by:
Hello,
I am running an sql statement that INSERTS INTO a table. If I run the
query using docmd.runSQL, it works fine - new records are added to the
table and duplicate records are disregared (...
|
by: Pathfinder |
last post by:
Hi All
I am trying to run the below but I receive the following error
"runsql action requires an argument consisting of an SQL statment"
Dim MySQL$
MySQL$ = "Select * from mytablename"...
|
by: Richard Hollenbeck |
last post by:
Help! I don't know why this isn't working:
Private Sub Combo9_Change()
Dim UsersCourseSelection As String
UsersCourseSelection = Me("Combo9").Value
Combo13.Visible = True
'the following...
|
by: RC |
last post by:
In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use
DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to...
|
by: Jimmer |
last post by:
What's better/ faster/ more efficient. Using Docmd.RunSql or Execute
to run sql? When is one better than the other or inappropriate to use?
Thanks.
jim
|
by: ben |
last post by:
I have the following code in a VBA module:
DoCmd.RunSql "Update tData Set sd = Log(Strike/Price) where symbol = '"
& symbol & "'"
This statement worked fine, and was using the built in math...
|
by: dstyles7 |
last post by:
there are two list boxes in a form i have a command populate button that takes the values from the list box and inserts the primary key value from the selected list box into a access table. The...
|
by: MitchR |
last post by:
Hello Folks;
I have run myself into a hole I cannot get of and need some assistance. I have a Form "Frm_ATandT_Activation_Center" that has a sub form "Frm_ATandT_Activation"....
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |