Executing SQL Statements in VBA Code

There are a number of ways to execute a SQL Data Manipulation Language (DML) statement from Microsoft Access, besides the obvious process of creating an Action Query and double-clicking its icon. Understanding your options with respect to executing SQL will make your code cleaner, give you more flexibility and we'll even throw in a great debugging feature as an added bonus.

The following article, while not exploring every facet and option, will demonstrate how to execute SQL using the following methods:

• DoCmd.RunSQL
• DoCmd.OpenQuery
• [Querydef].Execute
• [Database].Execute
• dbFailOnError

Saved Queries verses Embedded SQL

For the sake of this discussion, a differentiation will be made between a saved query object in Microsoft Access and a raw SQL statement.  When you read the word query in the text below, understand it to be a prepared, saved and tested Querydef object in Microsoft Access.  Read SQL to be raw, embedded SQL in the VBA code itself. 

This becomes important for the following reasons:

    The RunSQL object cannot execute a saved query
    The OpenQuery object cannot execute raw SQL
    The Querydef object requires a saved query

The demo download code for this includes a simple form that displays both the actual SQL statements and the VBA code to execute them.  The application is designed to stop in debug mode so you may follow the execution in the code module itself.  Saved queries are used where necessary but I have used embedded SQL everywhere possible.

RunSQL

RunSQL is a method of the DoCmd object in Microsoft Access.  It is designed for DML SQL, such as UPDATE, INSERT and DELETE statements.  You cannot "execute" a SELECT query so the RunSQL method will fail if you attempt to pass a select statement to it.

As mentioned above, RunSQL requires the actual SQL statement, not the name of a saved query.  The SQL string may be passed as a literal or through a variable, as follows:

      DoCmd.RunSQL "UPDATE titles SET price = price * 1.10

or ...

     sSQL = "UPDATE titles SET price = price * 1.10
     DoCmd.RunSQL sSQL 

The effect to the user is the same as if a query object had been double-clicked.  If warnings are enabled, the user will be informed of how many records will be affected and given the standard error report in the case of failures.  We will discuss errors in more detail shortly.

One advantage of this method is that it is a quick, simple way to execute simple SQL updates and deletes. The down side is that some SQL statements, especially inserts, can get very complicated very quickly so the sSQL variable becomes difficult to manage and debug.  In addition, if you do not want users to be bothered with the standard Access warning messages, you will have to toggle them off and back on after the procedure.

OpenQuery 

The OpenQuery method solves the first of the above-mentioned problems: knarly SQL statements.  It is very easy to create complex INSERT, UPDATE and DELETE queries from the Microsoft Query By Example (QBE) grid and save them as a Querydef object.  Once saved, they may be executed using the OpenQuery command of the DoCmd object.

  DoCmd.OpenQuery "qMkTbl_sales_bkup"

This does not, however, address the issue of warnings that require user intervention to complete the query transaction.  If you want to be sure that the query runs without the user knowing or being able to terminate, you need to turn off the warnings, like this ...

  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qMkTbl_sales_bkup"
  DoCmd.SetWarnings True 

Now, there's a slight issue with this approach as well.  It assumes that warnings are enabled.  What if the user already has them turned off?  Well, the above code will turn them on, which could irritate the user.  I once wrote some code to determine whether or not warnings were enabled and return the setting to the previous state after executing, but that is extra code, and there's an easier way to handle this issue.