Action queries: suppressing dialogs, while knowing results

        7 votes: ****      7,999 views      0 comments
by Allen Browne, 30 June 2006    (Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, 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:

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.

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

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


Other articles that may interest you:
Crosstab query techniques
What is SQL?
Calculated Fields

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Action queries: suppressing dialogs, while knowing results'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.