Why is my query read-only?

        4 votes: *****     3,41 views      No comments
by Allen Browne, 30 June 2006    (for Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne.  Created: June 2006.  Updated: May 2009

Why is my query read-only?

If you cannot edit the data in a query, this list may help you identify why it is not updatable:

  • It has a GROUP BY clause. A Totals query is always read-only.

  • It has a TRANSFORM clause. A Crosstab query is always read-only.

  • It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.

  • It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.

  • It involves a UNION. Union queries are always read-only.

  • It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.

  • It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.

  • The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.

  • The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.

  • The query is based on another query that is read-only (stacked query.)

  • Your permissions are read-only (Access security.)

  • The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)

  • The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.)

HomeIndex of tipsTop

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Why is my query read-only?'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).