MS Access Database Corruption

Mitigate the Effects of MS Access Database Corruption

The number of users that can network to a central MS Access database is proportional to the quality and bandwidth of the connection (e.g., network stability) and inversely proportional to the size of the database.

The more stress on these factors (i.e., high simultaneous user count, low network quality, & large file size) the greater risk of corruption to the database file.

Why Database Corruption Occurs

Corruption usually occurs in an MS Access database file in the following three ways, in this order:

  1. Inconsistent State:  This will prevent CC from connecting to the database but is also easily fixed by opening the database file using the MS Access program.
  2. Primary Key Loss:  Sometimes the primary key of a major table (e.g., Equipment or Calibration History) is lost, which has the effect of slightly slower access.
  3. Record Corruption:  This causes errors to occur in CC when any of the affected records are accessed.  This can also be fixed manually by opening the table with the corrupted records, placing any of the columns in Ascending order, and deleting those records that have nothing but number signs (######) in all of their fields.

Tips to Prevent Corruption

Other than migrating to a current SQL Server database, which removes all of these dangers, here are some things you can do to greatly lessen your exposure to MS Access database corruption:

  1. Backup:  Backup your database at least once per day on a physical drive and computer.  Consider automatically backing up ALL of your files, including databases, to a second local location (for easy access) in addition to an off-site (e.g., cloud) backup.
  2. Compress & Repair:  Open your Access database file at least once per week using MS Access and run the 'Compress & Repair' utility to reduce its file size.  Access database files have a tendency to become unnecessarily bloated.  Note that you will need to do this when no one else is using the database.
  3. Clear Change Log:  If you don't need them, consider deleting the contents of your Change Log (tblChanges) or at least records older than a certain date.  Compress & Repair after.
  4. Clear Sessions:  If you don't need them, consider deleting the contents of the Sessions Log (tblSessions) regularly.  Compress & Repair after.

Last Updated:  12 February 2018