Microsoft Access Tips for Serious Users

Provided by Allen Browne, June 2006.  Last updated May 2009.

Action queries: suppressing dialogs, while knowing results

Action queries change your data: inserting, deleting, or updating records.
There are multiple ways to run the query through macros or code.
This article recommends Execute in preference to RunSQL.


In a macro or in code, you can use RunSQL to run an action query. Using OpenQuery also works (just like double-clicking an action query on the Query tab of the Database window), but it is a little less clear what the macro is doing.

When you run an action query like this, Access pops up two dialogs:

A nuisance dialog:

nuisance dialog

Important details of
results and errors:

important dialog

The SetWarnings action in your macro will suppress these dialogs. Unfortunately, it suppresses both. That leaves you with no idea whether the action completed as expected, partially, or not at all.

The Execute method provides a much more powerful solution if you don't mind using code instead of a macro.


In a module, you can run an action query like this:
    DBEngine(0)(0).Execute "Query1", dbFailOnError

The query runs without the dialogs, so SetWarnings is not needed. If you do want to show the results, the next line is:
    MsgBox DBEngine(0)(0).RecordsAffected & " record(s) affected."

If something goes wrong, using dbFailOnError generates an error you can trap. You can also use a transaction and rollback on error.

However, Execute is not as easy to use if the action query has parameters such as [Forms].[Form1].[Text0]. If you run that query directly from the Database Window or via RunSQL, the Expression Service (ES) in Access resolves those names and the query works. The ES is not available in the Execute context, so the code gives an error about "parameters expected."

It is possible to assign values to the parameters and execute the query, but it is just as easy to execute a string instead of a saved query. You end up with fewer saved queries in the Database window, and your code is more portable and reliable. It is also much more flexible: you can build the SQL string from only those boxes where the user entered a value, instead of trying handle all the possible cases.

The code typically looks like this example, which resets a Yes/No field to No for all records:

Function UnpickAll()
    Dim db As DAO.Database
    Dim strSql As String

    strSql = "UPDATE Table1 SET IsPicked = False WHERE IsPicked = True;"
    Set db = DBEngine(0)(0)
    db.Execute strSql, dbFailOnError
    MsgBox db.RecordsAffected & " record(s) were unpicked."
    Set db = Nothing
End Function

The other difference is that Execute does not display the progress meter in the status bar (at the bottom of the Access window.)

Further suggestions:


The table below summarizes the difference between RunSQL and Execute:

  RunSQL Execute
Show warnings only if something goes wrong No Yes
Know how many records were affected (programmatically) No Yes
Roll back if something goes wrong No[1] Yes [2]
Use in code Yes Yes
Use in macro Yes No [3]
Use the Expression Service Yes No
Shows progress meter in the status bar Yes No

[1] Not reliably: Microsoft's knowledge base article 208184 explains that an action query may commit changes even when cancelled.

[2] You must use a transaction to roll back. dbFailOnError is not enough. In Access 97, the documentation is wrong on this point (corrected in the readme file.)

[3] You can use Execute in a function, and RunCode in a macro.

HomeIndex of tipsTop