Access 2010 - simple update query gives "Operation must use an updateable query"

[InstructionWhich7142]

Access 2010 - simple update query gives "Operation must use an updateable query"

UPDATE PrioStaff INNER JOIN wname ON PrioStaff.wn_ref = wname.wn_ref SET PrioStaff.wn_lvr = [wname].[wn_lvr];

Both are tables, why on earth doesn't this work?!?

(I started with something more complex and kept getting this error so reduced it as much as possible, I really don't get it)

edit: so I've tested it with primary keys set on both tables, not just the one I want to update and it works, buy WHYYYY?!? this seems a stupid requirement, one table is an import and doesn't get a key, or need a key! by default.

Should I just give up and use a dlookup to fudge it? (seems a bit weak)

 

[pr2-eugin]

Re: Access 2010 - simple update query gives "Operation must use an updateable query"

Are the fields you are trying to JOIN on Indexed? If not we are having a problem.

 

[InstructionWhich7142]

Re: Access 2010 - simple update query gives "Operation must use an updateable query"

They aren't, why is this a requirement?

 

[pr2-eugin]

Re: Access 2010 - simple update query gives "Operation must use an updateable query"

Because it is so. To know why your Query might not be updatable check out : http://allenbrowne.com/ser-61.html

 

[InstructionWhich7142]

Re: Access 2010 - simple update query gives "Operation must use an updateable query"

frustrating

 

[pr2-eugin]

Re: Access 2010 - simple update query gives "Operation must use an updateable query"

Indeed !

Explain what you want to do, maybe we could find you a solution.

 

[InstructionWhich7142]

Re: Access 2010 - simple update query gives "Operation must use an updateable query"

the dlookup route has sorted it, and as there's only a few hundred records and its an admin level user data refresh its not a problem