Most users of Microsoft Access have, at some point, encountered the message that their database has been corrupted or placed in an “Inconsistent State”. You will see a dialog with the message: Microsoft Access has detected that this database is in an inconsistent state, and the software will attempt to recover the database.
Since my start with Microsoft Access 25 years ago, the frustrating aspect of this error is that there isn’t a definitive reason why the database became corrupted. Error messages in a variety of other software programs are much more descriptive on what to do to prevent violating the software rules. In Access, for example, there are characters that are not allowed when naming fields in a table. If you choose a character that is not aligned to the rules, the message that follows is clear. Select the Help button and then follow the rules displayed regarding table name rules.
So, what does one do regarding database corruption in Access?
First, it’s important to know that most Access databases become corrupt at some point in time. I’ve found the most proven methods to deal with corruption are not to waste time trying to determine why the database got corrupted, but rather focus on methods that prevent the database from getting corrupted in the first place.
Listed below are some things that Arkware consultants do that have helped reduce database corruption significantly:
- Split your database into a front-end and back-end (two separate files). All the database tables are in the back-end database and each user has their own copy of the front-end database on the desktop or local hard-drive. The front-end database contains all the database objects (queries, forms, reports, and macros/VBA). Each PC has the same folder structure such as (C:\WorkingDatabase\DatabaseName). Using the Linked Table Manager, link the tables from the back-end database to the front-end database. Access has a database splitting wizard that can assist with splitting the original database into the front-end and back-end databases. Look for a future Blog entry on using this wizard.
- Force the database to be shut down at close of business or other specific downtimes. This is done because users sometimes leave the system open and the unattended Access connection may get disconnected from the network, which can cause corruption. The code can be placed on the “OnTimer” event of any database form that remains open. The best database form to use is a main switchboard form. Simply add DoCmd.Quit to the VBA code to close the system down at a certain time of the day (ie 2:00 am).
- Make sure all users are using the same version of Access on all computers.
- Create a MS-DOS command batch file that copies a new front-end database to the local C:\ drive of the user’s computer. This is not mandatory, but it does ensure that everyone receives a clean copy of the local front-end database.
- Do not have tables from other Access databases linked to your main database that are using different versions of Access.
- Disable Access auto-correct feature. This can be done by selecting File > Options > Current Database. Under “Name Autocorrect Features”, deselect “Track Name Autocorrect”
These are some of the main preventive measures to guard against database corruption. If you are struggling with database corruption, reach out to Arkware and we can assist you in achieving a more stable environment for your Microsoft Access database.