Access 10' with SQL Server DB 'Operation must use an updateable query'

Question

I have an Access 2010 'front-end' DB that has its data stored in a SQL Server DB 'back-end.'I'm trying to write the most simple UPDATE query I can and being met with this error: 'Operation must use an updateable query.' My qry code is:

UPDATE tblTableLastModifiedDates SET LastModified = NOW()
WHERE id='1';

A lot of this DB was set up before I got this job so the dumb table names are not my fault. Also, I checked the tblTableLastModifiedDates and it actually has no PK and I've been unable to create one. I'm not sure if that is the problem.

Before I scrap all this and start it over (to do it the right way), I figured I'd ask if anyone knew how to fix this error.

Mike Perrenoud · Accepted Answer · 2014-01-15 14:03:38Z

1

This error implies that tblTableLastModifiedDates is a view that is not unique enough to update the LastModified field in the underlying table. This isn't that uncommon.

The best approach is going to be to execute the UPDATE against the underlying table.

answered Jan 15, 2014 at 14:03

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Couchcommando Over a year ago

So, the data that the LastModified stamp is in reference too? Should I avoid using the tblTableLastModifiedDates? I'm sorry, I don't understand what this means: "a view that is not unique enough to update the LastModified field in the underlying table."

Mike Perrenoud Over a year ago

@Couchcommando, tblTableLastModifiedDates is a View logically. That means it's a SELECT statement that produces a result set and is a collection of tables. There are times that a View is in fact updatable, but there are times that the result set has duplicates and or a primary key field for the record you're trying to update is missing from the result set. That makes the View read only. I would recommend just updating the underlying table instead (the table LastModified is actually coming from).

Couchcommando Over a year ago

I used your suggestion and stored the information in the underlying table. Thanks!

Collectives™ on Stack Overflow

Access 10' with SQL Server DB 'Operation must use an updateable query'

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related