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
The following article, while not exploring every facet and option, will
demonstrate how to execute SQL using the following methods:
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
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
sSQL = "UPDATE titles SET price = price * 1.10
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.
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.
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 ...
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.