Messages such as “Unrecognized database format”, “The database needs to be repaired” or records that suddenly display as #Deleted are alarming, particularly when the business runs on the system. Corruption in Access is rarely random. It has a short list of causes, most of them preventable.
If it has just happened
What is done in the first few minutes matters more than anything else.
- Get everyone out of the database and stop people trying to reopen it.
- Copy the files before doing anything else: the back end, the front end, and the lock file if one is still there. Put the copy somewhere safe and leave it untouched.
- Work only on a further copy. Compact and Repair can rescue a file, and it can also discard damaged data permanently. It should never be run on the only copy.
- Find the most recent backup and check its date, so you know what the fallback is.
- Write down what happened: the exact message, who was doing what, and whether anyone’s PC or connection failed just beforehand.
With an untouched copy and a known backup, most situations are recoverable. Without them, the options narrow quickly.
Why it happens
An Access data file is shared directly by every user’s PC. There is no server program managing access to it. Each PC reads and writes parts of the file itself. If one of those writes is interrupted, the file can be left in an inconsistent state. Nearly every cause of corruption is a variation on that.
Interrupted connections
The most frequent cause is a network connection that drops while data is being written. Wi-Fi, VPNs, links between offices and failing network hardware all produce brief dropouts that other software shrugs off and Access does not.
PCs that stop without closing Access
A power cut, a forced restart, a laptop lid closed in the middle of an edit or a crash on one PC can leave a write half-finished.
Everyone sharing one file
When all users open the same file containing forms, code and data, they are all modifying one object at once. The remedy is the standard design: a back end holding only the tables, and a separate front end on each PC.
File-sync folders
Putting a shared Access database in a OneDrive, Dropbox or similar synchronised folder causes trouble. Those services copy whole files in the background and are not designed for a file that several people are writing to at the same moment. The result is conflicting copies and damaged data.
Mixed versions of Access
Users on different versions or builds of Access opening the same data file can cause problems, particularly when the versions are far apart.
Size
An Access file cannot exceed 2 GB. As a file grows towards that limit it becomes slower and more fragile, and reaching the limit stops the system outright.
Reducing the risk
- Split the database, with a local front end on every PC.
- Use a wired network for anyone who writes to the data.
- Keep the back end out of synchronised folders.
- Back up automatically and often, and test that a backup really does restore.
- Compact the back end regularly, when nobody is using it.
- Keep users on the same version of Access where possible.
- Protect the server and key PCs from power loss.
When corruption keeps coming back
A single incident after a power cut is bad luck. Repeated corruption is a sign that the system has outgrown a shared data file: too many users, too much data, or a network that cannot be made reliable enough.
At that point the lasting answer is to move the data to SQL Server. A database server handles every write itself and records each one in a log, so an interrupted update is undone cleanly. The Access front end, and everything users know about it, can stay.
Need help with this on your own system?
Talyon repairs, supports and modernises business-critical Microsoft Access applications. Describe what is happening and what the system does, and start with a conversation.