Limitations on Using Select Queries to Update Data

The recordset that Access creates when you run a query looks and acts pretty much like a real table containing data. In fact, in most cases you can insert rows, delete rows, and update the information in a recordset, and Access will make the necessary changes to the underlying table or tables for you.

In some cases, however, Access won’t be able to figure out what needs to be changed. Consider, for example, a calculated field named Total that is the result of multiplying two fields named Quantity and Price. If you try to increase the amount in a Total field, Access can’t know whether you mean to update the Quantity field or the Price field. On the other hand, you can change either the Price field or the Quantity field and then immediately see the change reflected in the calculated Total field.

In addition, Access won’t accept any change that might potentially affect many rows in the underlying table. For that reason, you can’t change any of the data in a totals query or in a crosstab query. A Group By field is the result of gathering together one or more rows with the same value. Likewise, a Sum is most likely the result of adding values from potentially many rows. A Min or Max value might occur in more than one row.

When working with a recordset that is the result of a join, you can update all fields from the many side of a join but only the non-key fields on the one side, unless you have specified Cascade Update Related Fields in the relationship. Also, you cannot set or change any field that has the AutoNumber data type. For example, you can’t change the ContactID field values in the tblContacts table in the Conrad Systems Contacts sample application.

The ability to update fields on the one side of a query can produce unwanted results if you aren’t careful. For example, you could intend to assign a contact to a different company. If you change the company name, you’ll change that name for all contacts related to the current CompanyID. What you should do instead is change the CompanyID field in the tblCompanyContacts table, not the company name in the tblCompanies table. You’ll learn techniques in Chapter 13 to prevent inadvertent updating of fields in queries.

When you set Unique Values to Yes in the query’s property sheet, Access eliminates duplicate rows. The values returned might occur in multiple rows, so Access won’t know which one you mean to update. And finally, when Access combines rows from different tables in a union query, the individual rows lose their underlying table identity. Access cannot know which table you mean to update when you try to change a row in a union query, so it disallows all updates.

Query Fields That Cannot Be Updated

Some types of query fields cannot be updated:

• Any field that is the result of a calculation

• Any field in a totals or crosstab query

• Any field in a query that includes a totals or crosstab query as one of the row sources

• A primary key participating in a relationship unless Cascade Update Related Fields is specified

• AutoNumberfields

• Any field in a unique values query or a unique records query

• Any field in a union query