Operation must be an updatable query - Access

Question

I'm writing a database and I simply want to update tblSchedule with the ItemNo from tblStock but I get an error when trying to run this:

Operation must be an updatable query

I can't seem to figure out why it's not working.

UPDATE [tblSchedule] 
SET [tblSchedule].ItemNo = 
         (SELECT DISTINCT Item 
          FROM [tblStock], [tblSchedule] 
          WHERE [tblStock].Bookcode=[tblSchedule].[PartCode]
         )`;

Any help would really be appreciated

possible duplicate of [Operation must use an updatable query. (Error 3073) Microsoft Access](http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access) — Filburt, Jul 23 '15 at 15:02
Thanks, I did read that article already but it didn't give a definitive answer, since it was old I posted again to see if there had been any further developments. I think I may have to go down the dlookup route (not that i want to) — Walshie1987, Jul 23 '15 at 15:35

score 0 · Answer 1 · answered Jul 23 '15 at 15:06

0

You are missing a closing bracket in your SQL.

UPDATE [tblSchedule] Set
  [tblSchedule].ItemNo = (
    SELECT DISTINCT Item 
    FROM [tblStock], [tblSchedule  -- Missing closing bracket
    WHERE ((([tblStock].Bookcode)=[tblSchedule].[PartCode]))
  )

Try closing the bracket on tblSchedule.

I do not have an Access database to test this on for you, though.

answered Jul 23 '15 at 15:06

Doesn't look like there's a missing bracket, even in the older edit? – Aravona Jul 23 '15 at 15:09
2

Apologies this was a typo as I tidied my tables names up for the example (my table name was "tblSchedule-WK30RealDataTest1" which i though was too long for an example). I've corrected this now. – Walshie1987 Jul 23 '15 at 15:11
No problem, @Walshie1987. Sometimes (for me) it is the simple stuff. – Jul 23 '15 at 15:15

score 0 · Answer 2 · answered Jul 23 '15 at 15:26

0

My guess is your inner SELECT is returning 2 records instead of one.

You can do this to validate.

SELECT Items.ItemNo, count(*) total
FROM 
(
   SELECT DISTINCT Sc.ItemNo, St.Item
   FROM 
      [tblSchedule] Sc INNER JOIN
      [tblStock] St ON Sc.PartCode = St.Bookcode
) as Items
GROUP BY Items.ItemNo
HAVING count(*) > 1;

answered Jul 23 '15 at 15:26

Juan Carlos Oropeza

47,252
12
78
118

Thanks, the output is 252, which I'm guessing is correct as there are 252 records that need updating. – Walshie1987 Jul 23 '15 at 15:33
Maybe i explain wrong. That query should return 0. Otherwise mean you are trying to update ItemNo with two values { value1, value2 } and that is wrong. – Juan Carlos Oropeza Jul 23 '15 at 15:41
That `distinct` in your query make me wonder if you mean something like `TOP 1` instead. But without knowing your schema i cant help. – Juan Carlos Oropeza Jul 23 '15 at 15:46

score 0 · Answer 3 · answered Jul 23 '15 at 15:58

Due to the simplicity of what I wanted I've gone down the Dlookup route which works successfully.

UPDATE [tblSchedule], [tblStock] SET [tblSchedule].ItemNo = DLookUp("Item","[tblStock]","[tblStock].Bookcode='" & [tblSchedule].[PartCode] & "'")
WHERE (([tblStock].[Bookcode]=[tblSchedule].[PartCode]));

It's probably not the best method but due to the small amount of records it updates (252) it works perfectly without any noticable time delay.

Thanks Again! Chris

Operation must be an updatable query - Access

3 Answers3