accdb database in inconsitent state
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...
Forum Discussion
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.
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.
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!
- 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.- 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...
