Operation must use an updateable query

[toveyj]

Hi

I am trying to update a linked table which is in a backend Access database on networked drive. To update this table I need to JOIN it to another linked table which is in an SQL server 2008 R2 database. I'm doing this in Access. My SQL is as follows:

UPDATE DART_New_UBRNs_Master
INNER JOIN ccgbish_tbl_drss_master ON left(DART_New_UBRNs_Master.INITIAL_UBRN,4) & mid(DART_New_UBRNs_Master.INITIAL_UBRN,6,4) & right(DART_New_UBRNs_Master.INITIAL_UBRN,4) = ccgbish_tbl_drss_master.UBRN_var
SET DART_New_UBRNs_Master.[NHS Number] = [ccgbish_tbl_drss_master]![NHSNO_var]

WHERE (DART_New_UBRNs_Master.[NHS Number] Is Null AND ccgbish_tbl_drss_master.NHSNO_var Is Not Null)

So I want to fill in the NULL values of [NHS Number] where it exists in the SQL table.
I get the error 'operation must use an updateable query'.
I have checked that there is a Primary key in the Access table as I have seen this quoted as a cause of this error.
I have also seen it suggested this error is caused by an INNER JOIN on a query but both sides are tables.
I have also checked that I can manually update the field DART_New_UBRNs_Master.[NHS Number] in design view
so it isn't anything to do with permissions to update the table. I have run out of suggestions I can find from
other forum posts.

This query has run with no problems for months. However I refreshed my link to the SQL
table this month as it had additional fields added to it which wouldn't appear in the Linked table until
I refreshed the link. There is no Primary Key in the SQL table but as far as I know there never was (it
is managed by another team).

Any ideas why this query is giving this error?

 

[xenou]

Linked tables in SQL server with no primary key are considered a cause of non-updateable queries, so you might as well just load the relevant data into a temp table in Access and run your update with that data (more or less the same sql can be used without UPDATE to get the records that need to be updated).

 

[toveyj]

OK thanks. Bit of a pain as I will have to create a temp table from the SQL table which has over a million records. I have found an old copy of the front-end database before I refreshed the Link to the SQL table and the query still works in there. Looking at the linked SQL table it wasn't indexed then either. So all a bit odd. But thanks for the suggestion.

 

[xenou]

Note: you can download the data to a temp table in another linked access db to avoid bloating the current one. It would need to be compacted regularly because of the number of records being added/deleted.