You can update a query or query field in the following cases:
- A query based on one table
- A query based on tables with a one-to-one relationship
- The query's results contain a Memo, Hyperlink, or OLE Object
Data is updatable under certain conditions
If a query is based on tables with a one-to-many relationship, you might not be able to edit the data for the following query fields.
| Query field | Solution |
|---|---|
| Join field from the "one" side | Enable cascading updates between the two tables. |
| New records, if the "many" side join field doesn't appear in the datasheet | Add the join field from the "many" side to your query to allow adding new records. |
| Join field from the "many" side, after you've updated data on the "one" side | Save the record; then you'll be able to make changes to the "many" side join field. |
| Blank field from the table on the "one" side of a one-to-many relationship where an outer join exists | Enter values in fields from the table on the "many" side, but only if the joined field from the "one" side contains a value for that record. |
| New records, if entire unique key of ODBC table isn't output | Select all primary key fields of ODBC tables to allow inserts into them. |
Data can be deleted but not updated
| Query or query field | Solution |
|---|---|
| Query (or underlying table) for which Update Data permission isn't granted | To modify data, permissions must be assigned. |
| Query (or underlying table) for which Delete Data permission isn't granted | To delete data, permissions must be assigned. |
| Query or query field | Solution |
|---|---|
| Query based on three or more tables in which there is a many-to-one-to-many relationship | Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates). |
| Crosstab query | None |
| SQL pass-through query | None |
| Query that calculates a sum, average, count or other type of total on the values in a field, or an update query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions | By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions. |
| Union query | None |
| Query whose UniqueValues property is set to Yes | None |
| Query that includes a linked ODBC table with no unique index, or a Paradox table without a primary key | None |
| Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view | You must join the tables properly in order to update them. |
| Calculated field | None |
| Field is read-only; the database was opened as read-only or is located on a read-only drive | None |
| Field in record that has been deleted or locked by another user | A locked record should be updatable as soon as it is unlocked. |