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

Or

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

The reason .Execute cannot resolved form references is due to the fact that the DAO object model has no visibility to the Access object model. RunSQL and OpenQuery, however, are part of Access object model and as such can resolve the references before passing the SQL request to the database engine for processing.

As an alternative to provide equal functionality in both contexts, some may wrap an Access object model reference with the Eval() function:

CurrentDb.Execute "qryMySavedQuery", dbFailOnError

Where the qryMySavedQuery has the following SQL:

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

This works because the VBA project has visibility of the DAO object model and the Access object model and thus can act as a bridge to connect the two object models. Eval() is an Access function that executes in the VBA project, thus it provides a simple solution to capitalize on the VBA projects' visibility of both object models. Using the Eval() function is useful when you need to be able to invoke the same query with all different methods.

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 a temporary table for staging and a 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.