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
7  5100 
Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem. Example: http://members.iinet.net.au/~allenbrowne/ser-37.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jimmer" <jr*****@hotmai l.com> wrote in message
news:70******** *************** **@posting.goog le.com... 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
Thanks.
I presume that excluding the Setwarnings statement. The execution time is the same?
Thanks again.
Jim.
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message news:<41******* *************** *@per-qv1-newsreader-01.iinet.net.au >... Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jimmer" <jr*****@hotmai l.com> wrote in message
news:70******** *************** **@posting.goog le.com... 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
Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?
Thanks.
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message news:<41******* *************** *@per-qv1-newsreader-01.iinet.net.au >... Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jimmer" <jr*****@hotmai l.com> wrote in message
news:70******** *************** **@posting.goog le.com... 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
Try it and see.
I doubt there will be much difference. Other factors are likely to loom
larger, such as whether the data is in cache, whether you are using
transactions, network traffic (if the data is on a server), ...
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jimmer" <jr*****@hotmai l.com> wrote in message
news:70******** *************** ***@posting.goo gle.com... Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?
Thanks.
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:<41******* *************** *@per-qv1-newsreader-01.iinet.net.au >... Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem.
Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html
"Jimmer" <jr*****@hotmai l.com> wrote in message
news:70******** *************** **@posting.goog le.com... > 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 jr*****@hotmail .com (Jimmer) wrote: Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?
One person stated in testing one particular update that Execute took 2 seconds while
docmd.runsql took 8 seconds. YMMV.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm jr*****@hotmail .com (Jimmer) wrote in message news:<70******* *************** ****@posting.go ogle.com>... Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?
Thanks.
No idea. Put a timer on your form and execute each one something like
10,000 times in a loop. Use GetTickCount and you'll have your answer.
Jimmer wrote: 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
RunSQL gives you nice progress meter, if the query is not very complex
then it's quite accurate :-)
--
\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 "UPDATE tblKlant " & _
"SET tblKlant.Bedrag = tblKlant.Bedrag + 10.96" & _
" WHERE tblKlant. = forms!!"
|
by: DCM Fan |
last post by:
Access 2000/Windows 2000 Pro with all current updates on the client; SQL Server
7.0/Windows 2000 Server with all current updates on the server.
Bottom line: In a certain context dbs.Execute strSQL, dbfailonerror would cause
Access to GPF, whilst DoCmd.RunSQL strSQL, True would perform as expected.
The context is nothing special, but MUST have something to do with it, even
though I've done this 1000X before. In the AfterUpdate Event of a...
|
by: DCM Fan |
last post by:
All,
A month or so ago I started a thread titled "I was *forced* to use RunSQL."
At that time, I said I'd get back to you after trying the dbSeeChanges constant
in the Execute method. I was very confident that it was going to solve the
problem.
It didn't. I've resigned myself to using RunSQL instead of dbs.Execute in this
context...first time ever. The bottom line from the JET lovers in here is that
|
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 SQL thing is all on one line in the actual code.
|
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 update 3 row(s)."
Is there a way to prevent the message from popping up?
| | |
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 Log
function.
In a separate module, I added the following function:
|
by: TriednTested |
last post by:
Hello
I have a subform and to edit the data I place it on the main form where changes can be made, then a cmd button is used to execute the docmd.runsql update query. However I am constantly getting an error msg (syntax error in UPDATE statement). Here is my code:
updateSQL = "UPDATE Assistance SET " & _
"Date =" & editDatetxt & ", " & _
"Assistance ='" & Me.aAssistancetxt & "', " & _
"Amount =" & Me.aAmounttxt & " " & _
"WHERE...
|
by: Trish |
last post by:
I have been trying to create a local table from a linked Excel
Spreadsheet using a simple query string like this:
SELECT * INTO LocalTable FROM LinkedExcelSheet;
If I use CurrentDB.Execute, I get Error 3078 - The Microsoft Jet
database engine cannot find the input table or query '128'. Make sure
it exists and that its name is spelled correctly.
If I use Docmd.RunSQL it works just fine. Why would that be?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
