Access Database is getting corrupt again and again

Thanks MarkMauger!

I likewise have developed this app for over 2 decades and it would take years to reproduce in a new code/database.

1. Finding the time to develop a sample that would duplicate the issue is complicated.
2. I need to look more closely at the list of the delete and edit queries and try to understand why it appears when it reaches a certain number of these, that it will go no further without the inconsistent issue.  Anyone have any ideas about this?

Regarding what I recently tested.

3. It seems very interesting to me that if the user who is going to initiate the morning updates, is first in, they will have no problem completing the task without the inconsistent issue.  But if they are second or later, then it will create the issue.

SO question: Is there some advantage or difference about being first in the ldb file that prevents the inconsistent issue?

4. If I verify how using the ldb viewer somehow negates the advantage of being in first, then what is up with that?
5. Unfortunately for me, the ldb file is a "black  box". I have now idea how it works with keeping track of locks or what else goes on in this file other than the computer and user names.  I would ask a lot of questions, but maybe this is not the thread for that.

This week we hope to have IT install the workaround on the new server to test further and ensure it solves the inconsistent issue and see how it affects performance.  But I would prefer trying to figure something out in Access that can side step the issue altogether and keep the benefits of leasing on the server.

Thanks!

Hi ShondaH1 !

Likewise have code in place that will close a session after an hour of inactivity after 3pm and then another closing all sessions after 11pm.  And I also have a custom compact utility that will close out all sessions automatically. Unfortunately there are a few situations that prevent the session from closing down.

1. If there is an unmonitored open dialog box, it will hang here.
2. If someone else logs into the computer while the frontend is open for the previous person, it will not close.
3. If the computer goes to sleep before the session has ended.

Compacting the database to get it back after an inconsistent issue, is complicated by sessions that keep the ldb file open.

As a last resort I use the ldb viewer, locate the computer and have it restarted.

I discovered that if the database isn't allowed to compact and compare (after sitting idle ...usually over night) we are guaranteed issues the next day. I added code to close the users databases at the end of their work day...resulting in much better performance now.

If you can create a sample database and sequence of steps and provide that to Microsoft, that would be a big help to them I think. Shane from Microsoft has contributed to this topic and I believe has said they are working on this problem, but the main thing they had trouble with was recreating the problem. Ultimately this is something that has to be fixed by Microsoft and not with the suggested server workaround. Some here suggest abandoning Access which I understand. However, I have a very complex and large Access application that has evolved over 20 years. It would take me years to rewrite it with other technologies. I hope Microsoft does not just give up on this as it has been devastating to many customers, end users and developers alike.

There is another client based Windows 10 registry change that is working for me so far (I would be out of business otherwise) that has been presented many times in this thread that you might want to check out. It does not require a server change, but It has been to done to ALL the Windows 10 PCs that use the application.

Please Microsoft fix this and don't give up on Access. Millions of database users still rely on it. I am glad it is still part of the current Office 365 offerings. I wish it was taken more seriously by Microsoft so the reputation of Access (and the users and developers reputations that use it) could be restored.

We did not want to use the work around for various reasons, and have been able to reduce the occurrence of the inconsistent issue.  With a new server about to replace an older one have considered using the work around, but wanted to do further testing.  Please see below.

Updates:

After some thorough testing where I was able to replicate the issue repeatedly I arrived at these observations.

We have a frontend-backend database which each morning is updated in such a way as to empty and re-populate and edit many tables on the backend. For years this was performed with or without multiple users logged in and there was never an inconsistent error. But after the Windows Update 1803, it was often at this point that there would be an inconsistent database error if there was any other user logged into the database. But it was not all the time, which seemed unusual, until some further testing was done. Now I can report the following conclusions.

1. When the user who will initiate the morning updates is the first one into the database, the procedure is accomplished without issue. Note: If other users were logged into the database after the first user, the procedure will still work.
2. If there is already someone logged in and then the user logs in who performs the updates, the inconsistent error always appears when the procedure is run.
3. The procedure is a sequence of multiple queries and macros with embedded queries. I could repeat the procedure under the same circumstances and the inconsistent issue would appear with the same query.
4. If I removed the query, the issue would still appear but later in the procedure. I could then remove that query and the issue would appear again later in the procedure with another subsequent query.
5. I broke the procedure up into three sub-procedures and the first sub-procedure would have no issue.
   1. The second sub-procedure would then produce the issue.
   2. If I logged out the other user who had logged in first, and then logged the user back in, and then initiated the second sub-procedure it would complete itself without issue.
   3. I could repeat step b with the third procedure without issue.

So, I hope this might provide some insight to someone who has more experience than me regarding Access and maybe come up with a way to "reset" something after each sub-procedure in such a way that I could have them all work without having to have other users logged out.  (We usually have everyone wait to get in the database until after the morning updates, but on occasion someone will already be logged in and then we have the issue.)

PS: One strange exception to point 1., if I used the ldb viewer to see which computers were logged in, and then closed the ldb viewer and then launched the procedure, it would fail. I have not taken the time to confirm this but will do so and provide additional feedback.

Notes: Backend Access2000 .mdb ; Frontend Access 2007 .accde ; Windows 10 with Access 2013. Have three different locations which use the database as indicated each with between 15 and 30 concurrent users.