Run Microsoft Access Action Queries in VBA and Hide Warnings without Using DoCmd.SetWarnings

Provided by Molly Pell, Senior Systems Analyst

Use the VBA DAO Database Execute command, rather than DoCmd.RunSQL and DoCmd.OpenQuery, to run action queries without warning messages and avoid changing the Access SetWarnings status.

Background

Action queries in Microsoft Access are queries that add, modify, or delete records from tables using INSERT, UPDATE, and DELETE commands (or create a new table via MAKE TABLE). 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 VBA 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.

One may assume all three are equivalent, but the first two should be avoided.

Problems with RunSQL and OpenQuery Commands

RunSQL and OpenQuery are the simplest to invoke, but they display the previously mentioned warnings, which require user interaction.

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

One option for suppressing the messages is to SetWarnings False before running the queries, then SetWarnings True after:

  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

This is not advisable for the following reasons:

Problem 1: Crash Could Leave Warnings Off

When you turn off system warning 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 if MS Access terminates unexpectedly. The result would leave Access in a condition where normal warning messages unrelated to queries are suppressed. This could let users accidentally delete objects without confirmation, close objects and modules without saving changes, etc.

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

Problem 3: 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.

For these reasons, it's best to avoid using the SetWarnings command at all. Fortunately, we have an alternative.

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 number (e.g. 0 rows when you expect > 0).
lngRowsAffected = dbs.RecordsAffected
  
dbs.Execute "DELETE FROM tblMyTable WHERE Bad", dbFailOnError
lngRowsDeleted = dbs.RecordsAffected

With Execute, you can avoid using SetWarnings because Execute doesn't show warning mesasges. As an added benefit, you can return the number 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).

Good luck!