This Recordset is not Updateable

Problem

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

Solution

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.)

source: http://allenbrowne.com/ser-61.html

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:

outsideinspect

Added a relationship between the two tables:

relationship

With this two changes, the recordset became updatable.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s