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

[Davef28]

Iam trying to run the following code. I have administrator rights on the db and for good measure have added my user to have full rights but get the above error. Any other ideas apart from a permissions thing ?

Thanks

It works when I run it against the same tables on a SQL Server using Query Analyzer


tsql2 = "UPDATE Local_IA_CUR SET NonBillHrsMktg = (SELECT Local_IA_CUR_MKTG.NonBillHrsMktg_dw"
tsql2 = tsql2 & " FROM Local_IA_CUR_MKTG WHERE Local_IA_CUR.mttk = Local_IA_CUR_MKTG.mttk)"

DoCmd.RunSQL (tsql2)

 

[workmad3]

Methinks the jet engine makes something read only by default, where sql server makes it read-write by default. If it involves cursors, have a look at making access cursors updateable(not sure how to myself, but I'm sure a quick ggogle will turn something up )

 

[Davef28]

Thing is, I can run this statement OK !

ALTER TABLE Local_IA_CUR ADD NonBillHrsMktg Numeric

 

[workmad3]

hmm

one other thing, I remember something in my databases lectures about certain things that make queries updateable. Something like the queries need to have the primary keys of all tables they are referencing in their view. It could be that SQL server is making sure the queries are updatable by doing this implicitly, while access is just being dumb and not letting you do so. Can't remember exactly what makes a view updateable, will dig out details if need be.

 

[Davef28]

details would be nice if poss, thks