UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> RunSQL vs Execute    
RunSQL vs Execute

This page is under consideration for merging with: Calling Action Queries

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.

Contents

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:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM MyTempTable"
DoCmd.SetWarnings True

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 include the statement in a dedicated exit point that will run even if an error occurs when the SQL is run. For example:

Private Sub MySub()
On Error Goto Err_Handler
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM MyTempTable"
Exit_Proc:
DoCmd.SetWarnings True
Exit Sub
Err_Handler:
'display some error
Resume Exit_Proc
End Sub

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:

CurrentDb.Execute "DELETE * FROM MyTempTable", dbFailOnError

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:

With CurrentDb
.Execute "DELETE * FROM MyTempTable, dbFailOnError
Debug.Print .RecordsAffected & " were deleted"
End With

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.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 69,655 times.  This page was last modified 12:03, 30 November 2013 by tina t. Contributions by George Hepworth, Brent Spaulding, Jack Leach and BananaRepublic  Disclaimers