Related Content:
    Calling Action Queries

Questions often come up in the community regarding how to turn off warnings when using DoCmd.RunSQL to run an action query. The reply is often a description on how to use DoCmd.SetWarnings, and also a suggestion to use the CurrentDb.Execute method instead.

RunSQL

DoCmd.RunSQL allows us to run an SQL Action string. By default, warnings for the database are turned on, which results in a prompt to continue to process the SQL. Many find this undesireable and would rather run the SQL string with no prompts. One such method is to turn the warnings off, run the SQL, then turn the warnings back on:

Turning warnings off to run the SQL is an acceptable method of accomplishing the task, yet we must be absolutely sure that the warnings are in fact turned back on. Failure to do so may cause unwanted record manipulation somewhere else in the application. The only way to be absolutely sure that the warnings are turned back on is to also include the statement in a dedicated exit point that will run even if an error occurs when the SQL is ran. For example:

The above ensures that warnings will never inadvertently be left off.

Execute

The CurrentDb.Execute() method offers many advantages over RunSQL. Trappable Errors, a Count of affected records, as well as no warning dialogs regardless of the SetWarnings setting.

The best advantage is the ability to trap and handle errors in the SQL processing: something that you can't do with DoCmd.RunSQL. To enable this, use the dbFailOnError option when executing the statement:

This redirects any errors encountered by Jet to your procedures error handler.

Additionally, the Execute method can be used to get a count on the number of records that were modified or deleted:

Because the Execute method is a part of DAO object model, it can be called from any DAO Database object, not just CurrentDb.

Many people find the Execute method to be a better solution because a) there's no requirement to explicitly handle warning messages, and b) you have the added functionality of error handling and the RecordsAffected count.

Evaluating Parameters with RunSQL or Execute

Another major difference between the two methods of executing queries is handling parameters, in particular the one that references Access objects. In short, RunSQL can resolve a form or report reference, but Execute cannot: therefore all expressions must be converted into their final values before running via the Execute method. Example:

DoCmd.RunSQL "DELETE * FROM MyTempTable WHERE ThisField = [Forms]![MyForm]![MyControl]"

CurrentDb.Execute "DELETE * FROM MyTempTable WHERE ThisField = " & Forms("MyForm").Controls("MyControl").Value, dbFailOnError

Using Execute, we can still make use of the Access object reference, though the expression must reside outsite of the query string:

CurrentDb.Execute "DELETE * FROM MyTempTable WHERE ThisField = " Forms!MyForm!MyControl, dbFailOnError

This is because Execute, being a part of DAO object model has no visibility to Access object while RunSQL and OpenQuery are part of Access object model and therefore can resolve the references before passing the request back to the DAO for final evaluation.

As one more alternative to provide equal functionality in both contexts, some may wrap an ES expression with a Eval() as thus:

CurrentDb.Execute "qryMySavedQuery", dbFailOnError

Where the qryMySavedQuery has the following SQL:

DELETE FROM MyTempTable WHERE ThisField = Eval("Forms!MyForm!MyControl");

This is useful when you need to be able to invoke the same query with all different methods. Thanks to Jet Expression Services, it can consume Access functions even though it may not have the visibility of Access objects and Eval() is an Access function which resolves such references.

Other Execution Methods

RunSQL and Execute are not the only means of executing a query programatically. Another method to note is the OpenQuery method. This works much the same as RunSQL when executed on an action query, but allows us to execute a query stored in a library database, without the specific database instance that the Execute method would require. One more potential reason to use OpenQuery is when you want to insert dirty data into temporary table for staging and running query may have some errors (e.g. duplicate key violation) but you'd rather get the partial result in as opposed to failing the whole operation and getting no data at all.