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
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
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
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
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