Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, June 2006. Updated April 2008.

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.

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:

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.

Execute

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:

• Add Error Handling to trap any error triggered by dbFailOnError.
• To get the SQL statement, you can mock up a query using sample values, and switch it to SQL View.
• For help with getting the quote marks right around text fields, see: Quotation marks within quotes.
• For optional criteria, build the SQL statement as demonstrated in this Search database.
• For an example of using transactions to roll back after an error, see: Archive: Move records to another table.
• For temporary tables, use a Delete query followed by an Append query. Make Table queries do not allow you do specify the field types, or set the field properties correctly.

Summary

The table below summarizes the difference between RunSQL and Execute:

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