Updatability (V2 and later)

      Comments Off on Updatability (V2 and later)

The recordset that Microsoft 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, any calculated field. If you try to increase the amount in a Total field whose value is a result of multiplying data in the Quantity field by data in the Price 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 total query or in a crosstab query. Access can’t update data in a field that has a Sum or Avg setting when the result might be based on the values in many records.

When working with a recordset that is the result of a join, Access lets you update all fields from the “many” side of a join but only the nonkey fields on the “one” side, unless you have specified Cascade Update in the relationship. Also, you cannot set or change any field that has the AutoNumber data type.

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 an order to a different customer. If you change the customer name, you’ll change that name for all orders related to the current customer ID.

What you should do instead is change the customer ID in the Orders table, not the customer name in the Customers table.

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 total or crosstab query
  • Any field in a query that includes a total or crosstab query as one of the AutoNumber fields
  • A primary key participating in a relationship unless Cascade Update is
  • Any field in a Unique Values query Any field in a UNION query