MS Jet Error: Operation must use an updateable query.

[TryfanMan]

Hi,

I am getting the error 'Operation must use an updateable query.' when I run an update query with subquery in MS access. Here is an example of the query:


UPDATE MatMonData SET MatMonData.prodHeadID = (
SELECT ProdHeader.prodHeadID
FROM ProdCalendar INNER JOIN ProdHeader ProdCalendar.prodCalID = ProdHeader.prodCalID
WHERE prodCalDate=matMonDataDateTime And wcName=matMonDataLineNo
WHERE MatMonData.prodHeadID Is Null;


As you can see I am using a subquery to populate a field with the relevant number (which happens to be a foreign key). The reason for this is I am importing a flat file, where the information is organised in the database in mulitple tables.

Not sure if this is enough information - let me know and I can supply more if needed.

Thanks for your help.

 

[mbreti]

Hello, I did not see any problem on the code that you wrote except that there is missing something that specifies on what fields are you performing the join. So after:

INNER JOIN ProdHeader

just include: ON

I hope this will work.

 

[TryfanMan]

Hi,

Thanks for your help - I'll give it a whirl.

Regards