Executing SQL Statements in VBA Code

Friday May 20th 2005 by Danny Lesandrini
Share:

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. This article demonstrates how to execute SQL using DoCmd.RunSQL, DoCmd.OpenQuery, [Querydef].Execute, [Database].Execute and dbFailOnError .

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.

Querydef Execute Method

Saved queries become part of what is called the Querydef Collection in Microsoft Access and you can manipulate them by setting an object reference.  It only takes a few lines of code and is very powerful:

Dim qdf As DAO.Querydef
Set qdf = CurrentDB.Querydefs("qGeneric")
qdf.SQL = "SELECT * INTO titles_bkup 
           FROM titles;"
qdf.Execute

This method suppresses the typical Access warning messages, such as "You're about to run a query that will ..." and the ones you get if there are any records that fail, along with a vague reason for the failure.  However, you will need to handle VBA errors, such as the one shown below.

Click for larger image

Here, I attempted to run a SELECT INTO statement, which fails because the table already exists.  However, the user need never see this message if your error handler is prepared to deal with anticipated issues such as this.

Database Execute Method

My favorite method for executing SQL, be it saved queries or raw SQL, is the Execute method of the Database object.  You can execute a statement with a single line, like this:

  CurrentDb.Execute "UPDATE titles SET price = price * 1.10"

In this case, CurrentDB references the currently open database.  Alternatively, you can create and load a Database object variable, which will allow you to accomplish more with this method.  After setting the variable, you can execute a query, supply additional options and even read the number of rows affected.  While this takes a few more lines, it is by no means verbose:

  Dim dbs As DAO.Database, sql as String, iCount as Integer
  Set dbs = CurrentDb
  sql = "DELETE * FROM stores WHERE state='WY'"
  '(not that there's anything wrong with Wyoming!)
  dbs.Execute sql, dbFailOnError
  iCount = dbs.RecordsAffected 

It should be noted that the RecordsAffected method also works with the Querydef object mentioned above.  There is overlap between the methods and use is probably more a matter of preference than anything else is.  Some people would rather keep all their SQL saved as queries, which is not a bad idea when it comes to debugging, but makes for a very cluttered query object window.

Another really, really great feature of these two object methods for executing SQL is the added debugging information you get when you add the simple option, dbFailOnError argument.  By adding this option, you force the code to halt and throw up an error message, which usually provides more detailed information than you would get by simply double-clicking a query in the query window. 

For example, I created a query to insert a new record into the titles table, but I deliberately omitted the price column, which is required.  Double-clicking an Append Query created to accomplish this simple task threw the following error:

Click for larger image

According to this message, all Access knows is that there was a validation error.  However, if you modify the dbs.Execute code above, adding the dbFailOnError message after the SQL statement, you get this message:

 

Now, that is useful!  In this case, I knew it was the price field that was causing the problem because I designed it that way, but what about when you are inserting thousands of records into a table with dozens of columns and one field of one record contains the wrong data type or missing data?  How would you ever find it based on the generic message that reads, "Validation rule violation?"  You virtually cannot. 

So, even if you want to use saved queries, keep this little trick handy.  When you get a cryptic error message upon execution of a query, press Ctl-G to bring up the Immediate window and type this in ...

  CurrentDb.Execute "qYourQueryHere", dbFailOnError

 

Best Practice 

I love best practice advice, but that just does not apply here.  There are, as you have seen, several different ways to execute DML SQL from Microsoft Access.  There are pros and cons, but ultimately it depends on what you need to accomplish, how much error information you need and want to collect and whether you prefer to keep your query objects to a minimum.  Give them all a try and you will probably settle on one you like, but keep that dbFailOnError option in your back pocket for emergencies.

» See All Articles by Columnist Danny J. Lesandrini

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved