IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, June 2006. Updated April 2008.
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:
 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.)
 You can use Execute in a function, and RunCode in a macro.
|Home||Index of tips||Top|
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...