Friday, November 20, 2009

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.

.

32 comments:

Anonymous said...

Roger: you did a great job! One of your tips on selecting a primary key inside the query that stopped been updatable after upsizing process - made my job easy!

Roger Carlson said...

Great! I'm really gratified to find that one of my posts has helped someone. Thanks.

Anonymous said...

Hi Roger,
Your blog helped me out with a problem I've been having with my database for the last few days.
Thanks
Keith

Anonymous said...

Thank you! I have been trying to figure this out for days. I have a query that includes data in a Linked excel table. The joins were fine for other form updates and I cannot create a primary key for the excel table. I changed the Dynaset on the form and it works now.

Anonymous said...

I have absolutely no idea what's going on with all this stuff about permissions and relationships.
A few years ago I was able to link an excel spreadsheet with 8 columns to an access database and update data easily and simply in access. Now I can't. Why? I have Office 2007 and I want to get shot of it because I have no clue how to fix this problem, it's just plain confusing. Why won't it work? I didn't change anything except upgrade(?) to 2007 and now I can't update my excel file in access. "Recordset not updateable" jeeeeezz. Help me please!

Roger Carlson said...

Good question! I think it's important enough to add to the main post. See my addendum above.

Anonymous said...

Thanks for the info on the lawsuit. I had no idea about this but your info is a great help!
I've chucked Office 2007 and installed Office 2002, good enough for me, and everything is as it should be! Thanks again... you've no idea how much searching I've done to get to the bottom of this problem.

Cheers!

Newdepts said...

Roger, so many reasons for the recordset not being updateable, I found out the hard way when moving from 2003 to Access 2007 in Win 7, that the reason was simply the Op system's folder permissions set to read only. Annoying! And why doesn't Microsoft follow through with some decent error info and help?

Anonymous said...

Thank you for this post. The lack of a unique index on an ODBC table in our test environment was causing problems, and your explanation was clear and concise.

Anonymous said...

I've made a web database in Office 365 (Sharepoint Online). The web database in Access 2010 allows a form with a query that uses dynaset - inconsistent updates. After syncing the Access Db with Sharepoint, the form in Access 2010 can edit the data in the table but the form in Office 365 (Sharepoint Online) does not edit the table data.

Any ideas.

Thanks

Anonymous said...

Thank you very much. I changed the field from index Yes duplicates to Index No duplicates and was able to fix it. I had a work around that did a select statement on the other table and was taken about 1 minute to run, now it runs in under a 3 seconds

glo said...

Thanks for posting this useful information. Setting the recordset to Dynaset(Inconsistent Updates) fixed my problem.

Richard said...

Hall of fame tip, Roger (Dynaset (Inconsistent Updates)). I could have saved myself 30 minutes if I'd come here first! Many thanks! Richard

R Collins said...

Roger, maybe you can help me. I am not an Access expert. I taught myself to use it by creating an educator database for all the teachers in my county. I have a schools table linked to a teachers table using a number unique to each school. This allows to enter new teachers by putting in their name, grade, and school ID number to do things like generate lists and labels. At the beginning of the school year, I update the database by going to my teachers query--which is naturally based on those linked tables--filter by school name, or by grade level, and then update the teacher's names. We upgraded to Access 2010 and now I can't update using anything using any of my queries. It's driving me to tears. I think perhaps the answer is in your blog post but I don't understand it well enough to track down the problem... without screwing things up even further. Isn't there a way to make all my queries updatable the way they were in previous versions of Access? I didn't change the design of anything but now my whole system of updating the database is defunct.

Anonymous said...

Really interested in an answer to this latest problem - 2010 seems like a different animal in so many ways. Simple things like updateable select queries that were so easy before now seem so difficult to resolve.

321jimk

Roger Carlson said...

RE: Access 2010 and Nonupdateable queries.

I have not experienced this myself, although I've seen rumblings around the internet about it. The first thing to try is to make sure both the front end and back end databases are in Trusted Locations

Anonymous said...

Thanks for the help Roger,

Saving my query defs with, "Dynaset (Inconsistent Updates)" made all my recordsource querries start working and my forms are working again!

Anonymous said...

Thanks for this! I had a query where I had hit the agregate (sum) button but did't realise. I had't selected anything to group on and so the result set wasn't being agregated at all and so I was really stumped what the cause was till I found your comprehensive list of what to look for! Works like a treat now!!!!

Anonymous said...

Hey Roger,

Your comments and suggestions pointed me to my solution.

Thank You
Mike e.

Anonymous said...

hey Roger

your excellent information keeps hel;ping people like me

thanks
Ken R

James Wollaston said...

Hi Roger,

I hit this problem when re-pointing an Access 2010 client to point to Access native tables instead of the original SQL Server linked tables.

Inconsistent updates was the solution.

Many thanks,

James.

Anonymous said...

wow, great detail. this helped me out immensely.

thanks man!
vic

Vivienne McLennan said...

Excellent - extremely clear and solved my problem in under 2 minutes!

Mike Donlin said...

Thank you so much for putting this together - saved me HOURS of work. I appreciate it.

Anonymous said...

Flippin' fabulous! :)

Anonymous said...

Reason #5: It is based on a Query that includes a linked ODBC table with no unique index.

I did not include the Key field nor * in my select statement, only a few non-index fields.

I can't thank you enough!

Anonymous said...

Roger you're the man. After 4 years your still saving lives.
Your tip about Union queries are not updatable and single tables queries are always updatable made me change the union queries I had to simple Or concatenations and it worked like a charm.
Thanks a lot and God bless you.

Unknown said...

Thanks Roger. I'm an untrained muddler and was able to understand your clear explanations and resolve my problem. As Arnie says "I'll be back . . ."

Anonymous said...

thanks didnt have keys set for some of the subsidary tables linked to the main table. all fixed

Ken Stuber said...

Your note on using Dynaset (Inconsistent Updates) for many-to-one-to-many relationships just saved my behind. Thanks!!!

Anonymous said...

Thanks!!..changed a field in one of the queries tables to Indexed (No Duplicates) and the problem was fixed.

ypsilon said...

Thank you Roger,
Your info about linked table from excel have helped me to understand why now we cannot update it anymore. That was a 'rich' feature.
Rgds
Karel