One of the most frequent questions I receive is about queries that won't update data. When you try to update a value via a query, Access tells you that the query isn't "updateable." That's not a bug at work; Access is working exactly as it should. When this happens, your only choice is to rethink the query's structure. The first step is to figure out why the query isn't updateable. The following queries aren't updateable:
- Any query with a GROUP BY clause.
- Any query with a TRANSFORM clause.
- Any query that uses an aggregate function (SUM(), MAX(), COUNT(), and so on) in the SELECT clause.
- Any query that contains a DISTINCT predicate.
- Any query that contains a UNION operator.
- Any query that contains a subquery in the SELECT clause.
- Any SQL Pass-Through query.
- Any query based on three or more tables where there is a many-to-one-to-many relationship.
- Any query that's based on a read-only query.
Sometimes a query that should be updateable isn't. Usually, something outside the query is the influence:
- The underlying tables might be read-only. Or the database is opened in read-only mode.
- The user must have permission to update the underlying data.
- The record may be temporarily locked. Try again later.
Once you know why the query won't update, you can rethink the query's structure to obtain a more flexible query.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.