Why is my query read-only?

        3 votes: ****      2,832 views      0 comments
by Allen Browne, 30 June 2006    (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: Poor Your rating: Not so good Your rating: Average Your rating: Good Your 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)
Verify Code
Verification Code

It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.