Access database is in inconsistent state ...

Question

I am running two identical MS Access applications - files are in the same location: back-end (tables only) on a server and multiple front-ends on workstations. the data structure is identical. Front front-ends are essentially identical. The only difference is the data. One runs perfectly. The other crashes once or twice a day. When it crashes we close the front-end apps. And open the back-end file to get the following error:

"Microsoft Office has detected that this database is in inconsistent state and this database will attempt to recover the database. During this process a backup copy of the database will be made and all recovered objects will be placed in a new database. ......"

The recovery takes one or two minutes, we are left with a back-up and all is OK.

However it is very disruptive to our operations. How can we stop this from happening? Can anyone in Microsoft analyse the file to determine what is causing the problem?

Server: Windows Server 2012 R2

Workstations: Windows 8.1

MS Access 2013 (MS Office 365 subscription)

PS: This is a well known and documented problem, appearing on a number of forums since 2010! but I cannot find a solution.

Answer 1

The Sydney app works perfectly. The Melbourne App has been crashing daily for about a month.

Are the back-ends being accessed over a local network, or over a WAN?  Is there a difference between the type of network access to the back-end for Melbourne vs. Syndey?

How big are the back-ends?  Is the Melbourne back-end pushing the 2GB file-size limit?

Could there be corrupt data in the Melbourne back-end?  Have you tried the following to eliminate that possibility, in increasing order of desperation:

1. Create a new blank database, import all the tables from the Melbourne back-end into it, and then see if it works as a substitute back-end without crashing?
2. Create a new blank database, importing just the *structure* of all the tables from the Melbourne back-end into it, then link to the Melbourne back-end and run append queries to load all the tables in the new database.  See if (a) all data comes over, and (b) it works as a substitute back-end without crashing.
3. Working with a a copy of the back-end:  export all tables to some other format, CSV maybe.  Empty all the tables.  Compact and repair the database.  Import the data for all the tables from the exported files.  See if *that* works.

Answer 2

Changing to SQL server is a good suggestion. However, the app as it is, has been working very well for more than 15 years. Migrating the data involves significant time and resources. I am just trying to keep things simple and cost effective.

Answer 3

I'm not very good on operating systems and networks. Here is some further info:

The back-end "accdb" file contains tables only and is located on a server. The operating system on the server is "Windows Server 2012 R2"

The front-end "accdb" files contain queries, forms, reports and VB code. They are linked to the tables in the back-end file via using the MS Access "Linked Table Manager".

The front-end files are located on workstations. The operating system on the workstations is "Windows 8.1".

There are two instances, because one is used for operations in Sydney. The other is used for operations in Melbourne. Otherwise the two instances are identical and operate side-by-side in the same folders. The only difference is the data in the tables and a few very minor differences in the VB code. 

A very simple arrangement, which has been working very well for 15 years.

The Sydney app works perfectly. The Melbourne App has been crashing daily for about a month.

Answer 4

This is indeed one of those errors that is very hard to debug, as Scott can attest. I would seriously consider upsizing the back-end to SQL Server. The free Express Edition may be good enough.

Answer 5

What is your network topography? How do users connect? Why are their two versions, what is the difference between them?

We have been wrestling with this same problem for several months now with a couple of our apps with no clear resolution though not with the regularity you are experiencing. There are many things you can try but this will take a good deal of troubleshooting with a lot more info. Lets start with the questions above and go from there.