Access Power Features   «Prev  Next»
Lesson 5Running queries with macros
ObjectiveRun different types of queries by using macros.
Besides opening forms, macros are very useful for opening Select queries and running Action type queries. Opening the Select style query is just a matter of using the OpenQuery macro action, then specifying the query to open in the arguments. You can see an example of this in this figure:
OpenQuery macro action
OpenQuery macro action
When there is only one macro in a group, you do not need to use a name at the macro level. The same is true when the macro is the first one in the group. However, when you use more than one macro in the group, the macro name tells Access that a new macro is starting.
Running an Action query requires a little more work. For instance, when you run an Action query, prompts will warn you about things such as running that particular query that will result in updating or deleting information from your tables. You can use a macro action called SetWarnings, which will cause Access not to display the prompts and warnings. As a result, Access will perform the default action for the warnings. For example, when you run an update query and get the prompt telling you how many records are going to be updated and asking you whether you want to continue, the default answer is Yes.
This is the choice that will be made when SetWarnings is set to No. Take a look at the macro in the figure below set up to use SetWarnings and run an action query:
Set up to use SetWarnings and run an action query
Set up to use SetWarnings and run an action query

Here are the settings for this macro:

Macro Action Arguments
SetWarnings Warnings On = No
OpenQuery Query Name = qryProjectRates
SetWarnings Warnings On = Yes

Remember to set SetWarnings back to Yes when you run queries at other times so that you will get the intended prompts.
If you run a make-table query and the table to be created already exists, the prompt defaults to NO when it comes to replacing the table. So if you run the make-table from a query, make sure you use the DeleteObject macro action to delete the table ahead of time.
In the next lesson, you will learn how to work with some other useful macros that are available in Access.
Running Access Queries with Macros - Exercise
Click the Exercise link below to practice running a query from a macro.
Running Access Queries with Macros - Exercise