Filipe Hemsworth Filipe Hemsworth - 1 year ago 87
SQL Question

MS Access "This recordset is not updatable" in a SELECT query

I'm stumped regarding this message MS Access is giving me.

I am trying to run a SELECT query, with two sub-queries within it. All tables are local.

Could someone please explain to me why the following code is returning this error?

SELECT S.SKU, S.Date, S.[Order No], P.WSP, P.Average_Cost, S.[Item Status]
FROM [Item Detail Temp] AS S, [FD Worksheets Temp] AS P
WHERE [P].[SKU]=[S].[SKU] AND [P].[Date to use]=(SELECT MIN(P2.[Date to use])
FROM [FD Worksheets Temp] P2
AND P2.[Date to use] >= S.[Date]);

Giving it a google hasn't particularly helped. I have Macros enabled in security to make sure that wasnt it, and from what I can tell I dont have any sort of Grouping in the code above that could cause the error?

Additionally, I believe the code does work just fine, but the whole time the loading bar is up that message is displayed in the bottom left corner of the screen. It also runs incredibly slow, and i'm sure i have run this query in the past without that message showing up, and also much quicker than it is currently.

Many thanks

EDIT: After looking a bit more, I have found a few possible reasons:

"- It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only. - There is a MIN() function, could that be causing it?

- It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead."

- The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields. There are no Unique Key fields, could that be the reason?

Answer Source

JOIN your table sources and use the domain aggregate function, DMin, to make your query updateable.

    [Item Detail Temp] AS S
    INNER JOIN [FD Worksheets Temp] AS P
    ON [P].[SKU]=[S].[SKU]
    [P].[Date to use]=
                "[Date to use]",
                "FD Worksheets Temp",
                "[SKU] = " & S.[SKU] & " AND [Date to use] >= " & Format(S.[Date], "\#yyyy-m-d\#")