A nuisance dialog:
Important details of
results and errors:
Provided by Allen Browne, June 2006. Last updated May 2009.
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: |
|
|
Important details of |
|
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.)
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 Use the Expression Service Shows progress meter in the status bar
[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.
| Home | Index of tips | Top |