With Action queries, you can easily modify
data without writing any code. In fact, using Action queries is often a
more efficient method of modifying data than using code. Four types of
Action queries are available: Update, Delete, Append, and Make Table.
You use Update queries to modify data in a table, Delete queries to
remove records from a table, Append queries to add records to an
existing table, and Make Table queries to create an entirely new table.
The sections that follow explain these query types and their
appropriate uses.
Creating and Running Update Queries
You use Update queries to modify all records or any
records that meet specific criteria. You can use an Update query to
modify the data in one field or several fields (or even tables) at one
time. For example, you could create a query that increases the salary
of everyone in California by 10%. As mentioned previously, using Action
queries, including Update queries, is usually more efficient than
performing the same task with Visual Basic for
Applications (VBA) code, so you can consider Update queries a
respectable way to modify table data.
Build an Update Query
To build an Update query, follow these steps:
1. | Select Query Design from the Queries group on the Create tab of the Ribbon. The Show Table dialog box appears.
|
2. | In
the Show Table dialog box, select the tables or queries that will
participate in the Update query and click Add. Click Close when you’re
ready to continue.
|
3. | To let Access know you’re building an Update query, select Update from the Query Type group on the Design tab of the Ribbon.
|
4. | Add to the query fields that either you will use for criteria or Access will update as a result of the query. In Figure 1, Ship State/Province is included on the query grid because we will use it as a criterion for the update. Shipping Fee is included because it’s the field that Access will update.
|
5. | Add any further criteria, if you want. In Figure 1, the criterion for Ship State/Province is CA. |
6. | Add the appropriate Update expression. The example illustrated in Figure 1 increases DefaultRate by 10%.
|
7. | Click Run in the Results group on the Design tab of the Ribbon. The message box shown in Figure 2 appears. Click Yes to continue. Access updates all records that meet the selected criteria.
|
You should name Access Update queries with the prefix qupd.
In fact, you should give each type of Action query a prefix indicating
what type of query it is. This makes your application easier to
maintain, and makes your code more readable, and renders your code
self-documenting. Table 1 lists all the commonly accepted prefixes for Action queries.
Table 1. Naming Prefixes for Action Queries
| Type of Query | Prefix | Example |
|---|
| Update | qupd | qupdDefaultRate |
| Delete | qdel | qdelOldTimeCards |
| Append | qapp | qappArchiveTimeCards |
| Make Table | qmak | qmakTempSales |
|
Access displays each type of Action query in the Navigation Pane with a distinctive icon.
|
Access stores all queries as Structured Query
Language (SQL) statements. You can display the SQL for a query by
selecting SQL View from the View drop-down list on the toolbar. The SQL
behind an Access Update query looks like this:
UPDATE tblClients SET tblClients._
DefaultRate = [DefaultRate]*1.1
WHERE (((tblClients.StateProvince)="CA"));
You
cannot reverse the actions taken by an Update query or by any Action
queries. You must therefore exercise extreme caution when running any
Action query.
