accdb database in inconsitent state

[Hans-DieterGehlen]

Hello everyone,

As of 1..2 weeks days we're encountering our accdb backend to get inconsistent regularly.

Background: We're running ca. 10 locally stored frontend accdbs which concurrently access a backend accdb hosted on a file share of a Windows 2012R2 domain/file server. The frontends are running on different versions of access starting with Access 2016, ending at Office 365 (C&R) -> Access Version 2307.

On our end, we change nothing. However - out of the blue - we encounter that some tables within the backend show up with much more rows (should be identical to the recordset.count value), than really exist. This leads to broken relations between items from different tables, which subsequently leads to a stucked Access most times. Sometimes one could heal by "repair&compress" but sometimes, the backend is within a state, where "repair&compress" could be started, but will then get stucked, as well.

We're using GUIDs as (primary-)indexes and the integrity check on all relations.

The size of the backend is ca. 23MB, so far away from the 2GB limit. 

As this really comes out of the blue, my gut feeling says it should be related with any kind of Windows/Office update. 

So, is anyone else here who encounters the same strange behavior? How to get to the root cause?

How to get rid of this? Productive operation is not possible at all in this buggy state.

Thanks!

[#2]

It sounds like the back end has been corrupted. It's a good idea to STOP doing anything with it until you correct that.
Corruption occurs for a number of reasons. One of the more common is connecting the Front Ends to the Back End over an unreliable network. Interruptions lead to incomplete data writes, and that corrupts the data. Wifi connections are especially risky. Users shutting down a computer with an active connection to the Access BE can also be the cause.

Trying to do a Compact and Repair over the network, i.e. leaving the accdb on the shared network folder and issuing the C&R from a local computer, also can be a cause of corruption, or more to the point, worsening it.

Start with a copy of the accdb in question. Bring it to your local computer. The first step is to isolate the bad records. Techniques to do that include searching, filtering, and sorting on the fields in the table. One or more records will show up with unfamiliar characters in one or more fields. Sometimes the Primary Key will appear to be duplicated in corrupted records.

Once you determine which records are bad, you can delete and recreate them.
Then do the C&R locally and replace the back end in the shared network folder.

A search on Bingoogle should turn up a number of discussions of recovery from accdb corruption with other suggestions on methods to try.

[#3]

Thank you George for your quick and comprehensive response.
However, everything is (should be) well known: No Wifi; no C&R via Network; Shutdown while leaving the beast open - unlikely; SMB1.0 enabled and enforced through GP within the domain (I don't know if that is still required _but that was a major reason for corrupted JET-DBs in the past_); Intermittent LAN cables - in doubt.
It gives no explanation to: worked for years, but as of two weeks (without any _visible_ changes) a few occurrences of heavily corrupted BE files.

Nevertheless: If you think I missed something or misinterpret - please correct me.

So even if I may be wrong and this is just a local issue of ours - Community: heads up if you _suddenly_ have the same experience. I'd be glad if you'd post this, a well!

[#4]

Your server is old. It could be having intermittent hardware issues. Access is very sensitive to momentary network continuity issues. Could be one of the switches as well.
I would definitely step up my backups. Every 15 min or so. And look for a new home for the BE, even a temporary one.

[#5]

Tom, Good point. Do you or anyone else have a ballpark number you think of the length of intermittents within the network? Is it <100ms, <1s or even longer which is needed to crash a JET DB? We're running a background timer within the frontends which will shut them down if the connection gets lost for >1s. That is: any user of ours would report if it happened. But no one did so far...
One more thing to add: MS Access never reported "database is in inconsistent state". That was my wording! What happens in real is that something happens in the background and days later we find Access getting stuck if we wanna get access to an entity which is broken inside! So, yes: we're doing server side backups every hour actually, but like Covid-19: at time of discovery, it is almost too late to move...