I have a Microsoft Access database application. When updating a record from the screen, users kept getting the error:
This recordset is not updateable
The form datasource is a query with two tables
This query was not updatable, here is a list of reasons why a query would not be updatable By Allen Browne:
- 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.)
- The fields that the query outputs are Calcluated fields (Access 2010.)
The query used two tables:
DMR and OutSideInpect
SELECT DMR.DMRid, DMR.logid, DMR.dmrstatus, DMR.datecompleted, DMR.pnrev, outsideinspect.dateinspect, outsideinspect.qtyrej FROM DMR INNER JOIN outsideinspect ON DMR.logid = outsideinspect.logid;
In this case the joining field had two issues:
- OpenSideInspect did not have a unique key
- There was no foreign key relationship between the tables.
Added a primary key to OpenSideInspect:
Added a relationship between the two tables:
With this two changes, the recordset became updatable.