Distinguishing Between Updatable and Nonupdatable Queries | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

All the examples in this chapter have updated table data via queries-updatable queries, to be exact. Sometimes a query isn't updatable (as is the case with some select queries based on multiple tables), which means that changes you make to the datasheet or bound form won't properly update the query's data source.

note

You might wonder whether there's any use for nonupdatable queries. The answer is yes, most definitely. Reports can (and often do) use nonupdatable queries based on multiple tables as their record sources, because reports don't update data, they only display it. Also, PivotTables and PivotCharts work fine based on nonupdatable queries. However, for most forms, you'll want to use either a table or an updatable query for a record source so that users can update the data.

Follow these rules for creating and using queries to update data:

Single-table queries are updatable because there are no relationships to consider. Whether you're working with a select query or an action query, simply specify fields and criteria, and then run the query. The SQL statement in such a query might be as simple as this:
```
 SELECT * FROM table WHERE criteria 
```
You can make any changes you want to such a recordset. The underlying data source might reject changes on the basis of incorrect data types or data that doesn't meet validation rules, but the query itself won't reject changes or updates.
A multiple-table query with a one-to-one relationship is updatable.
A query based on more than one table (or query) utilizes a join to combine data from the tables. Most of the time, the join will create a one-to-many relationship. You can change data on either side of the relationship as long as doing so doesn't violate referential integrity constraints. Subforms are a great way to handle one-to-many relationships, as opposed to basing a form on a multiple-table query. The main form contains the "one" records, and the subform displays related records for the current record in the main form.

See Chapter 5, "Creating Forms for Entering, Editing, and Viewing Data," for a discussion of using subforms to display data from linked tables.)

The following queries are not updatable:

Crosstab, SQL pass-through, and union queries
Multiple-table queries based on three or more tables when there is a many-to-one-to-many relationship
Any multiple-table query based on a many-to-many relationship
Any query that contains a totals or an aggregate function or that refers to a query or subquery that contains a totals or an aggregate function
Any query in which the UniqueValues property is set to Yes
Any multiple-table query in which no relationship exists between the tables
Any query in which the SQL statement includes a GROUP BY clause on the record source

Troubleshooting - An updatable query won't update

Sometimes an otherwise updatable query doesn't update as you might expect. Here are some possible causes:

You don't have permission to update the underlying table or tables. You should speak to the system administrator to gain the necessary permission.
The underlying table is read-only. You might be able to alter the table's state yourself, but most likely you'll need permission from the system administrator.
Another user has a record locked. In this case, you can usually update the data as soon as the record is unlocked.

tip - Know your updatable queries

You don't have to memorize all the rules for creating an updatable query to know whether the current query is updatable. With the query open in Datasheet view, check the bottom of the datasheet. An updatable query will display a blank row at the bottom, just like a table. If a bound form's navigation button isn't available, or if the navigation button or the New Record command is disabled, you might be dealing with a nonupdatable query. That's not always the case, however-some form properties prevent the user from adding new records. (For example, a form won't be updatable if its AllowEdits property is set to No.)

The rules for when a query can be updated vary a little when you're working with an Access project or a data access page, as follows:

A single table or join must have a primary key constraint, a unique constraint, or a unique index.
The results shown in a view or returned by a stored procedure that contains a join are read-only by default. You can set the form or page's UniqueTable property to a string expression that identifies the join's "many" table.
You can't update data based on a self-join.

For more details on self-joins, see Chapter 9, "Using Queries to Select Data."
You can update only the "many" side in a one-to-many relationship.