Executing SQL Statements in VBA Code - Page 2

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.

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:

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