Update Query - causing runtime error 3073 Operation must use an updateable query (1 Viewer)

[Number11]

so if more than one user is trying to run an update query we get this error? any idea's on how to make an update query multi user?

 

[gemma-the-husky]

No - it's not because multiple users are doing the same thing. The issue is that the particular design of the query makes the fields non updateable.

Eg - just open a simple query, and you will see that you can edit data directly in the query, thereby bypassing any checks you have within forms. Now if you take a totals query, you will find you can't edit the values in the query. Certain joins make a query non-updateable. The absence of indexes in tables can make a query non-updateable. So often you can get an updateable query by changing the way you approach the problem.

 

[arnelgp]

use form?
or add a semaphore table.
your code should try to lock this table first before attempting an update query.
your code should be able to retry (3 times?) to lock this table.
if it just cannot lock, exit gracefully.

if you able to lock the table, update it quickly and release the lock afterward.

this will prevent simultaneous update to clash.

 

[Number11]

use form?
or add a semaphore table.
your code should try to lock this table first before attempting an update query.
your code should be able to retry (3 times?) to lock this table.
if it just cannot lock, exit gracefully.

if you able to lock the table, update it quickly and release the lock afterward.

this will prevent simultaneous update to clash.

ok yeh so calling the update query from a button on a form, so how do i get the database to lock the table to allow the update and then cause this error

 

[arnelgp]

you can google it for now (i am out of my time-zone).
if nobody step in, you'll have to wait till tomorrow for my demo.

 

[arnelgp]

here is the demo.

 

[Number11]

here is the demo.

Thanks for the demo, so i just copy into my database the Modules and that will then work

 

[Number11]

Thanks for the demo, so i just copy into my database the Modules and that will then work

hope you get time to confirm

 

[isladogs]

It might help if you posted the sql for your non updatable query.
In the meantime, try setting Unique Records = Yes in the query property sheet.
In the query SQL that is UPDATE DIATINCTROW ....

If you still get the same error, there is another cause which may be obvious if we can see your query SQL