DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Database Corruption in MS Access

Have you ever had an Access database crash, and then not open again? This article is dedicated to the topic of database corruption and how to prevent it in ACCESS.

A database is said to be "corrupted" when a part of its data or functionality is lost. When an ACCESS database is corrupted, you may get several error messages while doing the common operations in ACCESS. For example, while opening and closing the database, while running forms or queries, while updating and saving records, or while scrolling through records. This is the indication that a part of your database is corrupted.

There are many reasons why this data or functionality corruption may occur.

(article continues after sponsor spot)



If the ACCESS database is only being used as the backend, while the front end application is based on some other software, then the front end may not have been structured properly and may corrupt the data or functionality.

Another common reason is a power outage during the data writing process in ACCESS. (I personally have small UPS units for each of my important computers, and I test them!)

If more than one user is trying to access and update the same piece of data at the same time, then data corruption may occur too.

Microsoft ACCESS has a default database engine, called the "Jet Engine" which is responsible for all the database tasks. A most common reason of database corruption is not using the correct Jet engine with your ACCESS database version. For example, for ACCESS 2000 the recommended Jet Engine is Jet 4.0 and for ACCESS 97 the recommended Jet Engine is Jet 3.5. Older engines cannot work on the new versions of ACCESS, so care should be taken.

You may know that the ACCESS application is based on VBA, or Visual Basic for Applications. That is why you need to install all the required components of VBA on your computer for the proper functioning of ACCESS. If, for some reasons, the VBA components cannot be loaded into the computer memory, then some of your ACCESS database files may become corrupted.

Poor database design is another corruption reason. You should properly normalize and design your database so that it can use the system resources properly and reduce the chances of database corruption.

Don’t use reserved words and characters to name the database tables, queries, forms, etc. This is another cause of ACCESS database corruption. Using a reserved word as the name of an ACCESS object confuses the software and affects its functionality.

When frequent update, delete, and edit operations are done in the database, the data becomes more and more fragmented on the hard disk. This may lead to database corruption in severe conditions. It is recommended that you compact your ACCESS database regularly. Backup before doing a compact.

You should always backup your ACCESS database regularly so that in case of any failure or corruption you may restore your full working ACCESS database.

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy