By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,628 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,628 IT Pros & Developers. It's quick & easy.

"Operation must use an updateable query"

P: 5
OS: Windows XP Professional
Microsoft Access 2003


I am trying to update a table in my DB from a tempory table. I need to ensure that if records in the main table match records in the temp table (on the keys) they are replaced/updated by the new data in the temp table.

the vba code i am running is below:

Expand|Select|Wrap|Line Numbers
  1.  Dim mySQL As String
  2.  
  3. mySQL = "UPDATE tab_Org"
  4.  
  5. mySQL = mySQL & " SET Org_Name = (SELECT temp_TabOrg.Org_Name FROM temp_TabOrg WHERE temp_TabOrg.ORGKey = tab_Org.ORGKey)"
  6.  
  7. mySQL = mySQL & " WHERE EXISTS (SELECT temp_TabOrg.ORGKey FROM temp_TabOrg WHERE temp_TabOrg.ORGKey=tab_Org.ORGKey);"
  8.  
  9. DoCmd.SetWarnings False
  10. DoCmd.RunSQL mySQL
  11. DoCmd.SetWarnings True 
This is the update on just one field, when i get it working (fingers crossed) it will update all fields in the main table except the key field. When i run this code i get the error "Runtime error 3073, Operation must use an updateable query." The general concessus in the forum is that this error is due to folder permissions etc. but I have checked these and I have the correct permissions, i have also tried to run the query on my personal computer and i get the same error. When i modified the query to not include the subquery in the SET part, instead just SET Org_Name = "some string" it works fine. Now if it was a permissions error i'm thinking that the query should still not work???

If anyone could help me fix the error or has a better way to achieve what i am trying to do it would be greatley appreciated

Thanks

Mike D
May 31 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mike,

Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim mySQL As String
  3.  
  4. mySQL = "UPDATE tab_Org INNER JOIN temp_TabOrg "
  5. mySQL = mySQL & "ON tab_Org.ORGKey=temp_TabOrg.ORGKey "
  6. mySQL = mySQL & "SET Org_Name = temp_TabOrg.Org_Name " 
  7.  
  8. DoCmd.SetWarnings False
  9. DoCmd.RunSQL mySQL
  10. DoCmd.SetWarnings True 
  11.  
Mary
Jun 1 '07 #2

P: 5
Hi Mary,

It works!!!!! thanks for the quick response : )


Mike
Jun 1 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary,

It works!!!!! thanks for the quick response : )


Mike
No problem Mike.

Glad you got it working

Mary
Jun 1 '07 #4

P: n/a
Hi i had the same problem!! and all the support forums refer to permission issues!!!

Thanks Mary !!! it works perfectly fine now with this query

Regards
Tassos
Oct 7 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.