1

I have searched on stackoverflow for this error message but none of the other situations/answers apply to me: every table in the database has a Primary Key, the database has no linked tables, and the queries are very simple. What's more, and possibly more significant, the queries usually work but sometimes don't; if a query fails then I usually try again (almost immediately) and then it usually works (and if it doesn't, I just retry several times and eventually it works again).

Here is just one example of the type of query I am using:

INSERT INTO GbpEx (GbpExDate,Currency,CurrencyID,GbpExRate) VALUES (43131,2,1,1331)

So it is very simple, no subqueries, and no linked tables. The table GbpEx has a primary key (all of my tables have a PK). And the query usually works but sometimes doesn't.

It isn't just INSERT; UPDATE queries also fail (and then work again moments later).

Here is my setup: I have an Excel application that connects to an Access database over a network. The Access database is not split, and contains Tables and Queries only -- no macros, no VBA, nothing. When the user starts the Excel app, the first thing the app does is write to the database with an UPDATE to make a note of when the user started the app. Usually this works just fine and the user can go ahead and use the app; sometimes this UPDATE fails (with the error message above), so the user tries two or three times and eventually there is no error and the app works as expected. Sometimes, the app fails in mid-operation (using a different query, for example the one above) with the error and the app stops; then the user tries again and all is well.

There are typically about 10 users, and maximum I have ever seen is about 20. Every time the app wants to interact with the database, it establishes a connection, performs its interaction, then closes the connection -- I close the connection as soon as possible so as to reduce the load on the database and the network.

I'm afraid I cannot reliably reproduce the error.

Here is another point: I thought it might be some sort of temporary read-only setting in the database, so I tried making the Access accdb file read-only. When I step through and the execution enters my error handling routine the error is the same as the one listed above, but there is an important difference: my error handling routine also writes to the database and so enters an infinite loop. However with the normal error it does not enter an infinite loop, so if the problem is because of the Access accdb file becoming read-only, it is for a fraction of a second (ie it becomes writeable by the time execution enters the error handler).

Before someone suggests SQL Server and the like -- yes, we have plans to migrate in the near future, but not just yet. And I would like to make sure that this problem will not carry over to another database.

Does anyone have any ideas please?

EDIT: Some more things I should mention:

This Excel app has been working just fine for several months and has only just (in the last couple of weeks) started displaying this behaviour. We also had to contend with the error Query '' is corrupt that was apparently caused by a Windows update in late November but we used Repair on Office and it went away (I suspect we did that at the precise moment that MS fixed the problem).

I have also tried the DISTINCTROW approach but that didn't fix it.

I have to emphasise that all of the INSERTs and UPDATEs are very simply and directly edit a Table -- no linked tables, no subqueries involved at all, no INNER JOIN, nothing. And they work most of the time and fail just sometimes.

Lewis Kirby
  • 67
  • 2
  • 10
  • *"Access database over a network"*: Despite your last paragraph I have to say it … while Access is a nice toy for small projects, I only can recommend a *real* database like SQL if you ever plan to have a life. I had so many strange behaviors out of the blue, and if Microsoft pushes an update they are very professional in [updating your database to death](https://accessexperts.com/blog/2019/11/13/critical-office-update-breaks-access-query-is-corrupt/). • Maybe make sure you update everything to the latest release (or not, depends on Microsoft). • Is `GbpEx` a linked table or a view? – Pᴇʜ Dec 16 '19 at 10:13
  • You might want to have a look here: [“Operation must use an updateable query” error in MS Access](https://stackoverflow.com/questions/19789709/operation-must-use-an-updateable-query-error-in-ms-access) – Pᴇʜ Dec 16 '19 at 10:13
  • GbpEx is a plain table; none of the writes to the database write to a view, and there are no linked tables. – Lewis Kirby Dec 16 '19 at 10:47
  • 1
    Thanks for the linked question, but I have seen that previously and none of the answers apply to my situation -- I am attempting to update a table directly with values, not using a SELECT in the INSERT or UPDATE, and I have no linked tables. The only thing that might possibly apply to my situation is the DISTINCTROW, which I tried last week and didn't help (sorry, I should have mentioned that). But thanks for the suggestion. – Lewis Kirby Dec 16 '19 at 10:50
  • By the way, I also suffered from the Windows update problem that caused "Query '' is corrupt" that was fixed my MS a few days afterwards. So this could be something similar -- given that the tool has been working fine for several months without this problem. I'll update my question with this information. – Lewis Kirby Dec 16 '19 at 10:52
  • Well then, it would be hard to remote investigate that issue for us. Seems to be an odd issue, since almost every answer reads like "I did X, Y or Z and it worked now but I have no idea why" so people seemed to change their query but did not find the reason. • Well this information *"has been working fine for several months"* makes me even more believe that this is either caused by an update (bug) or some network issue (we cannot see). – Pᴇʜ Dec 16 '19 at 10:57
  • 1
    Sounds like locking. Maybe the workload (or something in your app) has changed, so it happens more often. I too wouldn't use Access as backend for 10-20 users, and I wouldn't invest too much time, use that instead to plan the migration. – Andre Dec 16 '19 at 11:03
  • As workaround you could try something like: *If this error then* close connection, sleep 1 second, try again. Increase to 2 seconds if the 2nd try also fails etc. – Andre Dec 16 '19 at 11:05
  • @Andre Can anything really lock a table so that `INSERT` doesn't work anymore? Thought access locks record wise (so this could explain `UPDATE` issues but not `INSERT`). – Pᴇʜ Dec 16 '19 at 11:10
  • Save the query and call them. – Santosh Dec 16 '19 at 11:32
  • @Andre: Thanks I implemented that earlier this morning (retry max 5 times, with a 2-second pause) and I have had no reports of error so far, so fingers crossed ... – Lewis Kirby Dec 16 '19 at 12:18
  • @Pᴇʜ: Thanks for your comments and suggestions. I, too, am leaning towards a bug such as you suggest, and hoping that the migration to SQL Server (planned for mid-January) will fix this. – Lewis Kirby Dec 16 '19 at 12:20
  • @LewisKirby Never seen such strange issues with an SQL Server, so I bet this is an Access only *"feature"*. – Pᴇʜ Dec 16 '19 at 13:38
  • Okay, thanks everyone -- the consensus seems to be that it is probably an Access "feature" or something that has been broken by an update, or possibly due to the workload increasing steadily. In any case the consensus also seems to be that I should move away from Access to a grown-up database such as SQL Server (probably going to SQL Server Express; and my initial experiments with SSMA look promising). Thanks all -- but how do I close this? question? – Lewis Kirby Dec 16 '19 at 16:08
  • You can vote to close your question, leave it up to the community or delete it (if you think it is not useful for you or others anymore). – Pᴇʜ Dec 17 '19 at 07:20

0 Answers0