Microsoft Office Tutorials and References
In Depth Information
Chapter 3: Doing Neat Things with Action Queries and Query Wizards
✦ Delete records that meet certain criteria (delete query) .
✦ Copy data from one table to another table (append query) .
✦ Update (change) information in some records (update query) .
✦ Create a new table from data stored in other tables (make-table query) .
Make a backup before you run an action query. Action queries can make
huge changes in your database, and even if you’re careful, you may make a
mistake. Making a backup doesn’t take much time, especially compared with
the time it takes to recover from an action query’s unintended effects. You
may want to back up the whole database or just make copies of the tables
and the data in them that is affected by the query. (To find out about making
copies of a database object, see Book I, Chapter 2; to find out about backing
up a database, see Book VII, Chapter 1.)
The usual way to create a query is to click the Query Wizard or Query
Design button on the Create tab of the Ribbon. When you click either button,
Access automatically creates a select query. You can change the type of any
query, whether it’s new or well used. To change the query type, click the
Query Type buttons available in Query Design view on the Design tab of
the Ribbon. Make Table, Append, Update, and Delete are all types of action
Recognizing the dangers of the Run button
As you may realize by now, action queries make changes; they don’t just
display data. You need to know how to create an action query safely without
running it before you finish defining exactly how you want it to work. The key is in
when you use the View and Run buttons and how you open the query:
✦ When you work with a select query, the View and Run buttons do the
✦ When you work with an action query, the View and Run buttons do
completely different jobs:
• View: The View button displays Datasheet view with all the records
that match your selection criteria, which is a good way to preview
what records will change when you run the action query. The View
button is a safe way to look at the datasheet of an action query to see
whether the query will work the way you want.
• Run: The Run button executes the action — deletes or changes data
in your database. You can’t undo the action after you click the Run
button in an action query, so be very sure that you set up the query
correctly before you run it — and be sure to have backups of the
affected tables just in case disaster strikes. (To find out about
making copies of a table, see Book I, Chapter 2.)