This Recordset Is Not Updateable. Why?

This Recordset Is Not Updateable. Why?
by Roger Carlson

Introduction

Non-updateable recordsets are a problem that may have many causes and may produce many different error messages. Some of those include:

"This recordset is not updateable."
"Operation must use an updateable query."
"Recordset is not updateable" (seen in the status bar of a query, form, or datasheet view of a table.)

What does this mean? Well, sometimes you can edit data in the Datasheet View of a query to change the information in the underlying table. Other times, you can't. When you can't, the query is "non-updateable". When you try to create a recordset object based on a non-updateable query, the recordset becomes non-updateable.

The Microsoft Office Access Help system has a fairly extensive list that details when queries are updateable and non-updateable. However, this list is difficult to find. It is also in different places depending on the Access version you're using. You would think that typing "updateable recordset" would find the information, but it doesn't.

In Access 2003, you can find this information if you type: "When can I update data from a query?"
In Access 2007, type: "edit data in a query"

I thought it would be useful to list the information in a place that's a little easier to find.

When Recordsets Are Always Updateable

A recordset is always updateable when:

1. It is based on a single table.
2. It is based on a query based on a single table.
3. It is based on a query based on tables with a one-to-one relationship.

When Recordsets Are Never Updateable

A recordset is never updateable when:

1. It is based on a Crosstab query.
2. It is based on a Union Query.
3. It is an Aggregate Query that calculates a sum, average, count or other type of total on the values in a field.
4. It is an Update Query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions
   Note: By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
5. It is based on a Query that includes a linked ODBC table with no unique index.
6. The database was opened as read-only or is located on a read-only drive.
7. It is a SQL pass-through query.
8. It is a query whose UniqueValues property is set to Yes. (That is, it is a query with a DISTINCT predicate.)
9. Cartesian Joins (that is, a query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view.)
10. Query based on three or more tables in which there is a many-to-one-to-many relationship.
    Note: Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
11. Calculated fields. Even if the query itself is updateable, if a column in a query is based on a formula, the field cannot be updated. However, if the other fields in the formula are updated, the calculated field will automatically update.

Recordsets Are Updateable Under Certain Conditions

Some queries, especially those involved in a Join, will not be updateable under some conditions, but will be under others. In other queries, even if the query itself is updateable, some of the fields will not be. The following are cases of query problems and their corresponding solutions.

1. Query based on a Join of tables with no Relationship.

• Problem: If a query is based on two or more tables that DO NOT have a relationship established (with Referential Integrity enabled), the query will be non-updateable.
• Solution: Create a Primary Key or Unique Index on ALL of the fields used in the Join on the "one-side" table. To be clear, this means ONE primary key or unique index based on all of the fields, not separate indexes on each field.

In a query based on a Join of tables with a one-to-many relationship (1:M), you might not be able to edit the data in one or more fields. As the following examples show :

2. Join field from the "one" side

• Problem: If you have a 1:M relationship created between two tables, you cannot change the primary key field (used in the Join) of the table on the "one" side of the relationship.
• Solution: Enable cascading updates between the two tables.

3. New records, if the "many" side join field doesn't appear in the datasheet

• Problem: In a query based on a 1:M relationship, you can create a new record and fill in the fields that come from the "one" side table, but if the join field from the "many" side table is not visible in the query (that is, the foreign key), you cannot add data to the "many" side fields.
• Solution: Add the join field from the "many" side table (ie, foreign key) to your query to allow adding new records.

4. New records on the "one" side that are duplicates of other "one" side records.

• Problem: When adding a new record, if you try to type into the "one" side fields, you will be attempting to create a new record. Even if you use the same primary key values, it will give you an error.
• Solution: Add a value to the "many" side join field (foreign key) that matches the "one" side join field (primary key) of an already existing record. The "one" side values will simply appear.

5. Join field from the "many" side, after you've updated data on the "one" side

• Problem: If you are currently editing fields from the "one" side of the relationship, you cannot change the "many" side join field (foreign key).
• Solution: Save the record; then you'll be able to make changes to the "many" side join field.

6. New records, if entire unique key of ODBC table isn't output

• Problem: This is different than #5 under Never Updateable. In this case, the primary key of the linked ODBC table exists, but is not added to the query.
• Solution: Select all primary key fields of ODBC tables to allow inserts into them.

7. Query does not have Update Data permissions

• Problem: Query (or underlying table) for which Update Data permission isn't granted.
• Solution: To modify data, permissions must be assigned.

8. Query does not have Delete Data Permissions

• Problem: Query (or underlying table) for which Delete Data permission isn't granted
• Solution: To delete data, permissions must be assigned.

Conclusion

The causes of non-updateable recordsets are many and varied. Some have solutions and others don't. Hopefully, this list will help you know the difference.

Addendum (January 28, 2010):
This is in response to a comment below.  I thought it was important enough to add to the main article.

Linked Excel Sheets Not Updateable
The question is why are my linked sheets from an Excel Workbook non-updateable?  The answer is not technical but legal.  Several years ago, Microsoft lost a patent infringement lawsuit that involved the ability to update records in Excel from Access.  The upshot is that in Access 2003 and later versions, by design, you can no longer update data in Excel spreadsheets.

.

New Sample: Report_SpellCheck

Report_SpellCheck

by A.D. Tejpal

This sample db demonstrates spell check on a report prior to its opening. Two alternatives are covered:

(a) Record source is updateable.
(b) Record source is non-updateable.

Version: Access 2000 file format.
References: DAO 3.6

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=523

.

New Sample: AppendValuesToExcel.mdb

AppendValuesToExcel.mdb

by Roger Carlson

This sample demonstrates how to append data from a database to an Excel spreadsheet using Office Automation to find the next empty row in the spreadsheet.

You can fiind the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=522

.

New Sample: MovePriority.mdb

MovePriority.mdb

by Dave Mason

This sample allows the user to arbitrarily move records up or down in a form's Continuous Forms view.

You can find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=521

.

New Sample: MultipleLabelsOffsetBatch2.mdb

MultipleLabelsOffsetBatch2.mdb

by Roger Carlson

This is the latest sample in a family of samples that illustrate how to print multiple labels. Each sample gets progressively more complex.

1. MultipleLabels.mdb ( intermediate )
   This form illustrates how to print multiples of specific labels.
2. MultipleLabelsOffset.mdb ( intermediate )
   This sample is a more complete version of MultipleLabels.mdb. It is meant to be a simple minded stand-alone address label program.
3. MultipleLabelsOffsetBatch.mdb ( intermediate )
   This sample is a similar to MultipleLabelsOffset.mdb, but prints multiple labels for multiple people in a batch.
4. MultipleLabelsOffsetBatch2.mdb (intermediate)
   This sample is a similar to MultipleLabelsOffsetBatch.mdb, but adds the ability to have different numbers of labels printed for each address.

[top]

New Sample: Form_Treeview

Form_Treeview

By: A.D. Tejpal

This sample db demonstrates treeview control with two way synchronization visa-vis conventional form. Two styles of treeview control are covered as follows:

1 - Employees organization chart:

• 1.1 - Treeview at left shows the organizational set up in hierarchical style, department-wise, as per chain of command, while the adjacent subform at right displays the same information in conventional style.
• 1.2 - Name of employee in current row of subform is highlighted in light green. Name of top boss for this employee is highlighted in light maroon while all intermediate superiors in the chain of command reaching up to current employee are highlighted in light orange.
• 1.3 - In synchronization, corresponding nodes in treeview (current employee, top boss and intermediate superiors of current employee) get highlighted in colors matching those in 1.2 above.
• 1.4 - Treeview - Drag and Drop feature:
  Any employee node can be moved to another employee or department node. The resulting change in command structure gets reflected promptly in the treeview as well as the subform. If an employee node is dragged to empty space within treeview control (instead of specific destination node), a new department gets added, with the dragged employee designated as top boss in that department.
• 1.5 - Complete two way synchronization between treeview and the subform (Navigation / Editing):
  For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa.

2 - Student grading:

• 2.1 - Treeview at left displays classes and students while the adjacent treeview shows subject-wise grades for current student.
• 2.2 - Subject-wise grades can be entered / edited conveniently just by clicking the pertinent check boxes.
• 2.3 - For convenient viewing, as soon as a given class node caption is clicked or navigated to (say by Up / Down arrow keys), it expands, displaying all student nodes belonging to that class. Simultaneously, all other class nodes get collapsed.
• 2.4 - Complete two way synchronization between treeview and the subform (Navigation / Editing):
  For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa.

Note: For editing the label caption for employee or student nodes in the above treeviews, select the node by clicking on its label. Thereafter, click again so as to make the contents editable.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=518

[top]

New Sample: Form_SearchAllFields

Form_SearchAllFields

by A.D. Tejpal

Explanatory Notes

This sample db demonstrates fast search across whole data source, covering all fields and all records. The search is conducted on all columns in "Across and then Down" style. Based upon space separated search words entered by the user, advanced search capability is provided as follows:

(a) Match field values having any of search words.

(b) Match field values having all of search words, but not necessarily in the same order as entered in search box.

(c) Match field values having all of search words, in the same order as entered in search box.

(d) Exact match of field values against full contents of search box.

As the user types into the search text box, equivalent criteria string gets displayed at bottom of form. Simultaneously, the total number of matches found across the data source gets displayed.

In addition, all matching fields get highlighted as follows:

(a) Light Maroon: If the record carries a match in any column and it also happens to be current record

(b) Light Green: All other records having a match in any column.

Various search options are selectable via an option group. Appropriate command buttons enable the user to find the first or next matching record as desired. A special subroutine ensures that the found record gets displayed at mid-position of the subform window.

In order to facilitate efficient use of FindFirst / FindNext methods of a recordset, different data columns are stacked into a single master column through a union query. This temporary query, generated in form's load event, is devised in such a manner that different fields of a given record appear as a set of contiguous single field rows. These sets of rows (a set representing one original record) follow the prevailing sort order of form's record source. This is achieved by using sequential numbers for records (as per their prevalent sort order). Within a given set of rows representing an original record, the sort order is based upon tab index of respective bound controls.

Note:
(a) While building the union query, care is taken that only visible bound controls (or unhidden columns in case of datasheets) are included in its output.

(b) For insertion of sequential numbers, conventional approach, using a subquery or recordset based function can prove un-acceptably slow for large data sets. In this sample db an exceptionally fast method using incrementing variable, with values stored in a collection, has been used. This is based upon the technique kindly suggested by Gustav Brock. Since values displayed by such a method tend to be volatile on navigation through normal query output, a temporary table has been used for holding the values.

(c) If the current record has more than one field matching the criteria, it will take that many extra clicks of FindNext command button to move over to next record having a match.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=517

[top]