"Operation must use an updateable query" error

[Philocthetes]

Hi folks,


I'm trying to build what I thought would be a simple update query. The update source is a SQL Server table connected as a dbo object.



My query reads:

UPDATE UberTable INNER JOIN dbo_DPS_ItemWide_Recent_95_Days ON UberTable.[Bib ID] = dbo_DPS_ItemWide_Recent_95_Days.BibID SET UberTable.Post_Scan_Processing_Complete = [dbo_DPS_ItemWide_Recent_95_Days].[Milestone_ImageProcessing]
WHERE (((UberTable.Post_Scan_Processing_Complete) Is Null) AND ((dbo_DPS_ItemWide_Recent_95_Days.AggregationCodes) Like "*UFETD*"));

I've learned how to resolve (barrel through?) "This recordset is not updateable" problems, but this one is very confusing because the query that spawns the errror is an update query.

Regards,
G.W.


p.s. The field properties appear to be identical in target and source for the update.

 

[isladogs]

There are several reasons for this error which usually occurs when it's not clear to Access which records should be updated. Possible reasons include
1. The table(s) don't have a primary key field. This is ESSENTIAL
2. The type of join may cause issues unless you set Unique records =yes which is the same as using UPDATE DISTINCTROW ....
3. Your SQL table includes a Boolean field with no default value set and some NULL values (though that can cause a write conflict error instead)

If none of those are the issue, this link may help https://www.fmsinc.com/microsoftaccess/query/non-updateable/index.html

 

[arnelgp]

use 'Left Join' instead of 'Inner Join', then youll get the result you want.