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.
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:
One may assume all three are equivalent, but the first two should be avoided.
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:
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.
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.
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.
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).