Use the Execute Method to Suppress Warning Messages while Running Action
Queries in Microsoft Access Programmatically (VB6/VBA)
Provided by: Molly Pell, Technical Project Manager
Action queries in Microsoft Access are queries that add,
modify, or delete records from tables using INSERT, UPDATE, and DELETE
commands. When you run Action queries interactively, Access displays two
messages. The first is a warning, which asks you to confirm:
The second displays the number of rows that will be
affected:
When running Action queries in code, you generally do
not want to display these messages, since they require a user response
before the code can continue.
VBA offers the following methods for executing Action
queries in code:
-
RunSQL
Executes a SQL statement, not a saved query. Shows messages that
require user interaction.
-
OpenQuery
Runs a saved query, not a SQL statement. Shows messages that require
user interaction.
-
Execute
Executes either a SQL statement or a saved query. Does not show
messages that require user interaction. Allows you to return the #
of rows affected.
Issues with RunSQL and OpenQuery Methods
RunSQL and OpenQuery are the simplest to invoke, but
they display the previously mentioned warnings, which require user
interaction. One option for suppressing the messages is to SetWarnings
False before running the queries, then SetWarnings True after:
DoCmd.SetWarnings False
' OpenQuery executes a saved query
' cstrQueryName is a constant with the name of an action query
DoCmd.OpenQuery cstrQueryName
' RunSQL executes a SQL string
DoCmd.RunSQL "DELETE FROM tblMyTable WHERE Bad = True;"
DoCmd.SetWarnings True
This is not advisable for the following reasons:
Issue 1: Impacts Warning Setting
We’re assuming the user has warnings turned on. If the
user wants warnings off (which, of course, is not advisable), we’ll
annoy them by turning the back on. Extra code is required to check the
user’s preference before invoking SetWarnings.
Issue 2: Number of Affected Rows
Your application and your users have no way of knowing
how many rows were affected by the query. That information may be useful
if you’re expecting rows to be affected, but a data error causes no rows
to be affected.
Issue 3: Crash Could Leave Warnings Off
When you turn off system messages, you need to be very
careful to turn them back on in case of an unexpected error or crash.
Turning warnings back on in your error handler helps the situation, but
does not handle cases when Access terminates unexpectedly.
On Error GoTo PROC_ERR
DoCmd.SetWarnings False
DoCmd.OpenQuery cstrQueryName
DoCmd.RunSQL "DELETE FROM tblMyTable WHERE Bad = True;"
' If it crashes before it gets here,
' warnings are not turned back on!
DoCmd.SetWarnings True
PROC_ERR:
' This is better, but not perfect.
' What if Access terminates unexpectedly?
DoCmd.SetWarnings True
Solution: Use the Execute Method
As you can see, while RunSQL and OpenQuery start off
simple, they end up causing you a lot of extra work. The OpenQuery
command is really designed for displaying a SELECT or CROSSTAB query for
the user to browse. For Action queries, use the Execute method to run
saved queries and SQL strings:
Dim dbs As DAO.Database
Dim lngRowsAffected As Long
Dim lngRowsDeleted As Long
Set dbs = CurrentDb
' Execute runs both saved queries and
SQL strings
dbs.Execute cstrQueryName, dbFailOnError
' Get the number of rows affected by the
Action query. You can
' display this to the user, store it in a table, or trigger an
' action if an unexpected # (e.g. 0 rows when you expect >0)
lngRowsAffected = dbs.RecordsAffected
dbs.Execute "DELETE FROM tblMyTable
WHERE Bad", dbFailOnError
lngRowsDeleted = dbs.RecordsAffected
You can avoid using SetWarnings because Execute doesn’t
show warnings. As an added benefit, you can return the # of rows
affected by the most recent action query. You can show this value to
users, store it in a table, or use it to check for an unexpected result
(e.g. 0 rows affected when you expect >0).
Additional Resources
Good luck!
Return to the tips page.
|
