Understanding Action Queries
Action queries aren't quite as useful as select queries, because they tend to be less flexible. You create an ideal query once, and reuse it over and over. Select queries fit the bill, because you'll often want to review the same sort of information (last week's orders, top-selling products, class sizes, and so on). But action queries are trickier, because they make permanent changes.
In most cases, a change is a one-time-only affair, so you don't have any reason to hang onto an action query that just applies the same change all over again. And even if you do need to modify some details regularly (like product prices or warehouse stocking levels), the actual values you set aren't the same each time. As a result, you can't create an action query that can apply your change in an automated fashion.
But before you skip this chapter for greener pastures, it's important to consider some cases where action queries are surprisingly handy. Action queries shine if you have:
Testing Action Queries (Carefully)
In the wrong hands, action queries are nothing but a high-tech way to shoot yourself in the foot. They commit changes (usually to multiple records), and once you've applied the changes, you can't reverse them. Some database fans avoid action queries completely.
If you do decide to use action queries (and there are plenty of handy tricks you can accomplish with them), then you need to take the right precautions. Most importantly, before you use an action query, make a database backup! This step's especially crucial when you're creating a new action query, because it may not always generate the result you expect. To make a backup, you can copy your .accdb database file (just like you would any other file; one way is to right-click it, and then select Copy). If you don't want to mess with Windows Explorer, then you can create a backup without leaving Access by selecting the Office button Manage Back Up Database (Section 1.3.1).
Tip: It's always easier to make a backup than to clean up the wake of changes left by a rampaging action query.
Backups are great for disaster recovery, but it's still a good idea to avoid making a mistake in the first place. One safe approach is to start by creating a select query. You can then make sure your query's selecting the correct records before taking the next step and converting it into an action query (by choosing one of the action query types in the Query Tools | Design Query Type section of the ribbon).
The Action Query Family
Access has four types of action queries: