We have an Access Database that lives on a server in a shared folder.
A few months ago the database was unopenable due to error messages like:
- The document caused a serious error the last time it was opened.
- Microsoft Access has detected that this database is in an inconsistent state and will attempt to recover the database.
I was able to fix the issue by running a "Compact and Repair" from Access and that seemed to have fixed it.
The issue has kept happening though, and every time it does I run the same procedure:
- Go into the server with the shared file and in Computer Management close all "Open Files" connections to the file and folder.
- Open the database and run the "Compact and Repair"
- If the database created a backup file, usually called "Backup of [Name of original file]", move it to a separate folder to avoid confusion about which file to open.
This procedure seems to fix the issue every time, but I feel that this shouldn't have to be done constantly. Some times the error will happen a few weeks after the last fix, but sometimes this happens a day after I last fixed it.
What I have learned is that multiple people (usually a max of 3-4 at a time) will access this database at the same time, and it is not uncommon for those who use it a lot to leave it open all day. Sometimes I see that users have left it open for days without closing it.
Is there anything I can do to prevent this from being a recurring issue? Maybe Access isn't really meant to be used like this? Is there a check for a better consistency/checksum for the database to prevent issues?
I have researched and it seems that this is a needle in the haystack, it could be any number of network issues or other things.
11 Answer
Hopefully I can guide you in the right direction, because you gave no information about the setting (network/ how is Access set up):
The Jet/ACE database engine is by default multi-user -- it was built from the ground up to be that way. So that can be ruled out as cause
Sharing a Jet/ACE data store is very reliable when the network is at standard level. Standard means LAN over cable (Not WAN / DialIn and NOT wireless, because the bandwidth has to be sufficient for Jet/ACE to maintain the LDB file - for multi-user locking)
The normal usage of the database means a ping by the local PC's instance of the Jet/ACE database engine once per second (using it with default settings), and because Jet/ACE can't recover from a dropped connection (which might be a common event in a wireless environment. - So if you have now users over WiFi and before in "cabled" environment or those "leaving Access open for days" are via WiFi you should check for thatThe problem case where Access fails with "grandeza" is when a front-end Access application MDB is shared. The reason it fails is because you're sharing things that can't be reliably shared and have no reason to be shared. Because of the way Access objects are stored in a MDB file (the entire Access project is stored in a single BLOB field in one record in one of the system tables), it's very prone to corruption if multiple users open it. Sharing an Access front end (or an unsplit MDB with the tables and forms/reports/etc. all in one MDB) is the source for 95% of corruptions of Access/Jet/ACE files. - If that is the case you should consider rewriting the crucial parts of you application.
Very seldom cases happended with installing a new anti-virus SW or SW update to the server - this caused by locking files the JET/ACE engine to run havoc. Luckely this could be easely identified by looking into the event logs on the server and getting a patch from the SW vendor fixed the problem in almost all cases (when having a top-10 AV-SW a lot of companies are affected so the issue is resolved quickly)
And yes some of my clients in the past argued always when hitting scenario 2 or 3 "But it has worked up to now" or "we did it that way over some years". Instead in going into long winded analysis why starting with a certain point in time (mostly due to adding forms/queries or more users used WiFi) it started to fail, I removed the issue and all worked fine from then on.
EDIT
The Network of the OP is going over some floors/buildings. This should be no problem as long as the network is reliable (= connection in the network stays stable - so resending of packets is NOT a problem for Access). Or to phrase it in OPs word
"occasionally fails on a single ping that this could be the issue"
No this can be ruled out. But if MAN/WAN HW elements are used (like GSM/UMTS/LTE connectors, over the air VPN, only antenna connected network parts or similar) this might cause problems - Here only an in depth analysis of your logs can help or a network test with logging on. You would go for a lot of failed pings/resent packets in a short amount of time, not the usual noise of occasional errors you have in any network, but for dropped /reset connections, reconnection to a server etc while working in Access
The OP commented that everything is in a big file (database and frontend). To solve this you have to split the database. Here is an example: How to split an Access Database
