Solved "Operation must use an updateable query" but only sometimes (1 Viewer)

[Alc]

I know there are a whole range of causes for this message. What I can't find is a reason why a given query would produce this error then run without any problems a minute or two later.

I'm getting it from different queries which run as part of a nightly refresh.
All of these have run hundreds of times in the past and, without any changes being made to them, run with no errors later the same day.

Usually, if something goes from erroring to working in the space of five or ten minutes, it's because the network connections here are somewhat flakey and a connection to a linked table drops out then returns. In the case of this error, though, it even sometimes happens when inserting a record into a local table, so I know that's not the problem.

 

[Ranman256]

1 big rule:
you cannot run an update query if the TOTALs is on. (summation sign)
nor any of the subqueries in the update that uses Totals.

 

[Alc]

Thanks.
Yes, I found ten different potential causes and have gone through and checked that none apply.
If it happened every time, it might be easier to identify, but this is happening with the same queries.
Sometimes they run, sometimes they error.

 

[isladogs]

There are more than ten reasons for this behaviour.
You will see that message whenever the query is read only either due to its design or because another open object is using that recordset.
The latter reason is what may make queries read only intermittently

See Allen Browne's article Microsoft Access tips: Why is my query read-only? (allenbrowne.com)

 

[Alc]

Thanks.

I've found a workaround for it, but have no idea why it's working when the previous method (which has been in place, unchanged, for over five years) has started throwing up these errors.

The original method had Db.Execute followed by a SQL statement which included a passed variable. This had started erroring

The new method is to store the SQL as a query, obtain the SQL from the query definition, use the replace function to add the passed variable, then use Db.Execute to run that. This works.

I used debug.print to compare the old and new SQL statements being run and they're identical, so no idea why one runs and the other doesn't.
Oh, the joys of VBA.

 

[Pat Hartman]

There is no reason why your change should work. It is more likely that the change caused Access to rewrite the p-code and that fixed something.

 

[stefzg]

I registered to contribute here. A legacy Access application (that we're replacing in the coming months) has worked flawlessly for many years until a couple of months ago with sporadic errors of this type. Every weekly run imports 400K records into a large table for further processing and I would get this error between once and 30 times for each weekly run. Very annoying.

I tried an "on error / goto" loop but that was simply ignored by this error message and it would stop anyway. What appears to have worked now is to introduce a DoEvents / Sleep(10) sequence just before the CurrentDB.Execute statement. I assume that there was simply a write lock that persisted for a very short amount of time from the last insert statement and that would sometimes cause the error. Perhaps there was a change on the filer or on one of our endpoint security applications. Or in Access 2016.

 

[isladogs]

Welcome to AWF.

For over 20 years, I have always used a short pause using DoEvents , Sleep or similar between each item e.g. when importing several CSV files in turn. This is partly done to prevent any issues and also to allow a progress bar to update successfully

I am not aware of any changes in newer versions that would explain your recent issues. It might be a network issue in your case