473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

DoCmd.RunSQL/OpenQuery v. Database.Execute

twinnyfo
3,653 Expert Mod 2GB
Friends,

This question is in reference to another thread, but one response in particular, in which one of the Experts recommended
Forget DoCmd.OpenQuery() and forget DoCmd.SetWarnings(False) and use Database.Execute().
(here is a link to that post).

Most folks who know me well on this forum know how much I respect the other experts on this forum, because I have learned so much over the years. So, I, once again, am seeking better undestanding of my craft.

So, this is what I know, after doing some independent research on this subject. This is not exhaustive, but for those who are newer to MS Access/VBA, it should give a good overview.

-DoCmd.RunSQL and DoCmd.OpenQuery are very similar methods. .RunSQL accepts a properly formatted SQL String, whereas .OpenQuery uses a predefined query.
--One advantage is that these methods are very easy to use.
--The biggest disadvantage is that the normal popups for action queries are present unless one suppresses these popus using the SetWarnings method.
-Database.Execute can accept either a predefined Query or a SQL String.
--One advantage is that this method runs faster, because it operates at the DB Engine level.
--This method also allows the user to determine how many records are affected by the transaction and it provides a bit more meaningful error descriptions, allowing for better troubleshooting.
--One "disadvantage" of this method is that the SQL string must be fully formulated before it is executed--so references to form controls must be explicit--thus, the SQL that is executed must be complete and properly formatted with all parameters resolved.
In all the forums I've seen discussing these two methods, the PRIMARY argument aginst using RunSQL/OpenQuery seems to be (Heavens to Betsy!!!) that one were to turn the Warnings off and then turn them on again. Oh... and by the way, the .Execute method is a bit faster.

So, in a roundabout way, here is my question: Does one's selection of method for executing action Queries really matter?

The reason I ask is twofold:
  1. I have hundreds of RunSQl and OpenQuery references in my main DB. I use many of these during my weekly DB updates and transfer all the new data downloaded from our data systems. These all work perfectly and I have never had any issues with them. They also seem to work reasonably quickly (granted I am not dealing with millions of records...). Making this switch would be extremely time-intensive, but if it is really worth it, I am willing to put in the time.
  2. I have some action queries that would "fail on error" using the .Execute method. For example, There are times that I refresh certain tables with newly updated data. The easiest way to do this is to append the changes which could include duplicates, which would violate certain key restrictions. .OpenQuery with warnings off runs fine. I am concerned about having to add countless additional Error Handling routines to capture these situations just so that it runs by itself.

As usual, any advice is appreciated. I am always eager to understand more of the tools at my fingertips.
Sep 26 '18 #1
4 3992
NeoPa
32,556 Expert Mod 16PB
Hi Twinny. Good question.

I didn't even realise that Form references were not resolved in the .Execute method. I don't like such dependencies between objects as it seems very unclean to me so I avoid that like the plague anyway.

BTW .Execute can also run from other objects than just DAO.Database ones. TableDefs and QueryDefs spring immediately to mind.

Let me start by saying that I agree, using DoCmd. is not really a problem as such. I wouldn't bother recoding your whole history simply to use what I consider a cleaner and more logical interface.

Why do I see it as cleaner?
This comes from the concepts of OOP as far as I understand them. I was very much a procedural programmer by training. I started looking into OOP back in the eighties and mostly liked what I saw. Soon after that my career went away from mainstream programming for many years, so the fact that I had no proper training in OOP may leave gaps. I'll just say how it seems to me.

It seems that DoCmd is like some mega-object that provides many links into how things were done before objects came onto the scene properly, and also like a magic link into the operator interface. Office was using macros and VBA didn't yet exist. DoCmd provides a way to continue to do things that way, while doing it as part of an object structure. .Execute() OTOH deals with objects directly and appropriately. If you want to see what object the SQL is executing on, then just look at the value before the .Execute().

In most cases when executing SQL from within code, you really don't want warnings coming up. Warnings are an operator-relative option. They are there because DoCmd provides a simple way to call operator activities from code. If code couldn't handle the operator interface then things could get awkward and messy. Coders hate to see things available to the operator that they can't access themselves via code. Been there; done that; not good!

So there are many things that can be done both ways. I find the DoCmd way generally clumsy and impure from a coding perspective. At the end of the day though, it gets the job done.
Sep 26 '18 #2
twinnyfo
3,653 Expert Mod 2GB
Thanks, brother!

I knew I could count on you to add a dimension clarity and sanity.

Along these lines, however (because I have not played with this method at all), speaking in general terms, how does one manage the dbFailOnError when there is an error in the Action Query? I am not asking for detailed code snippets, but more of an "approach" to dealing with potential errors. (I hope this follow-on question is still on topic!)

I am looking into using .Execute in new sections of my code. As I learn and understand more about this method, I think it will benefit my projects.

Thanks again. Hope this thread hepps others out there with similar questions.
Sep 27 '18 #3
NeoPa
32,556 Expert Mod 16PB
TwinnyFo:
How does one manage the dbFailOnError when there is an error in the Action Query?
That all depends on what you're trying to do. Sometimes you treat it like a transaction so if any fail then you roll back. Other times you can (and I have examples of this) rerun it without dbFailOnError set and simply log that you had problems with such and such a query within a batch.
Sep 27 '18 #4
NeoPa
32,556 Expert Mod 16PB
PS. I just had an issue this morning where, because a developer had been using DoCmd.RunQuery(), all the users have their warnings turned off all the time. One of these users had clicked on the Delete Record button a number of times when the system was in unresponsive mode - but there was no prompt to confirm. This really doesn't make sense for an operator, and can be dangerous. I had to dig up a backup to compare the live data with the older version and see which records had been deleted. None had been as it turned out. She explained afterwards she believed the cursor was in the New record position at the time, but you can see how such things can cause issues.
Sep 27 '18 #5

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

Similar topics

0
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...
4
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...
7
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...
8
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...
6
by: David | last post by:
I am trying to insert an employee number into the EmpNbr field in my main table from a form where I add a new employee to my employee table. I was hoping this command would work, but it isn't. ...
2
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...
9
by: Tomba | last post by:
I have a really annoying problem, which i tried solving for almost 3 days now. (I googled in any variation i could think of, but i couldn't find anything that will help my stupid me understand:\)...
4
TheSmileyCoder
by: TheSmileyCoder | last post by:
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...
5
by: ghstarks | last post by:
I’m familiar with Access but have run into a road block with a project I’m working on and need your help. My desire is to provide my client a report, "Watch List Item Totals Type," with a chart...
2
by: bankowiec31 | last post by:
Is it possible to use the command "DoCmd.RunSql" in relation to other database than the current database? ACCESS 2010 code: Dim db1 As Database, db2 As Database Set db1 = CurrentDb Debug.Print...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.