This question is in reference to another thread, but one response in particular, in which one of the Experts recommended
Forget(here is a link to that post).DoCmd.OpenQuery()
and forgetDoCmd.SetWarnings(False)
and useDatabase.Execute()
.
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 theSetWarnings
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.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
--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.
.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:
- I have hundreds of
RunSQl
andOpenQuery
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. - 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.