"Operation must use an updateable query" error (1 Viewer)

Philocthetes

Has a great deal to learn
Local time
Today, 10:08
Joined
Dec 19, 2017
Messages
28
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:
Code:
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

MVP / VIP
Local time
Today, 17:08
Joined
Jan 14, 2017
Messages
18,186
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:08
Joined
May 7, 2009
Messages
19,169
use 'Left Join' instead of 'Inner Join', then youll get the result you want.
 

Users who are viewing this thread

Top Bottom