DoCmd.RunSQL/OpenQuery v. Database.Execute

[twinnyfo]

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.SetWarnin gs(False) and use Database.Execut e().

(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.Execut e 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 "disadvanta ge" 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.

[NeoPa]

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.

[twinnyfo]

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.

[NeoPa]

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.

[NeoPa]

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.