When Access is not used properly, database corruption can often occur which results in an unustable database or a database that experiences random errors or performs inconsistently.
What is Corruption?
A corrupt database occurs when the database has lost some of its data or functionality. This usually occurs when there is an error in the database’s underlying binary file format.
Access, along with its Jet Database Engine, has the power to automatically fix minor database corruption issues while performing normal tasks like reading and writing data to the tables, without informing the user. Minor database corruption updates are quite common and most of the time you won’t even know a correction has been made since Access fixes the problem automatically for you behind the scenes. However, if a corruption issues is not automatically fixed by Access and continues to exist, it will often spread and grow continually worse. We have extensive experience diagnosing corruption problems. Please contact us to see how we can help you diagnose and resolve corrupt databases.
How do I know if my Database is Corrupt?
An Access database not only consists of data, but database objects as well. Database objects are defined as tables, queries, reports, forms, macros and modules. Corruption errors are usually uncovered when performing functions: such as deleting records, accessing the database and its objects or its data. For example, you may receive an error message when opening and closing databases, running reports, forms or queries, updating records and/or scrolling through data records.
What is the cause of Database Corruption?
There are a number of factors that can lead to database corruption. A few are outlined below.
Hardware Failures
Hardware issues with networking equipment and hard drives are the most common issues that affecting database integrity. With this in mind your hardware should be regularly tested to prevent database corruption. Network cards can lose packets or hard drives can experience bad sectors which are contributing factors not only to database corruption but to other computer failures as well.
Microsoft Access has the potential to crash if there is a hardware failure on your machine. Because the underlying Jet Database woks at the file-system level it is extremely sensitive to an application. As a result, if your Access application crashes, the Jet Database also immediately terminates any writing without finishing. This does not occur in database such as SQL Server since they are based on a client-server.
Plug-In Software Conflicts
Database corruption can result if you use 3rd party Access plug-ins (including 3rd party ActiveX controls) that have not been designed properly. These plug-ins often run in the same application memory space as MS Access since they are also programs, so a plug-in crash will result in an MS Access crash as well.
JET Database Engine Conflicts
The Jet Database Engine has undergone four major release version updates in addition to a variety of service pack changes. If multiple users with different service packs or updates share the same database there is a potential that database corruption will occur. Specifically, this is caused by the minor format differences within the mdb file the locking mechanism. The latest version of the Jet Database has greatly reduced the possibility of database corruption; however the potential for corruption still exists.
Multiple Users
Unlike a client-server based SQL Server, the Microsoft Access file-based Jet Database Engine utilizes a Lock file to synchronize read/writes which allows multiple users to share the same file over a network. File synchronization is handled by Windows OS API calls which is dependent on file-transfer network protocols. It is important to note that network connections all have bandwidth limits so any other software transferring data across the same network socket could result in the potential slow down in the communication of Jet. The connection will often disconnect when the slow down occurs due to a time out failure. As a result, the database remains in a suspect state and will often require repair before the user can continue processing.
Sometimes network problems can result in the database being in a ‘suspect’ state even though it does not cause corruption. However, it all depends on what user function was being performing at the time of the problem. For example, a user attempting to append large SQL queries when the network connection was lost, will most likely result in a corrupt database.
TIP: If you store a file on a Windows NT server, you may want to disable ‘opportunistic locking’ on the server. While opportunistic locking can improve file performance, it also results in increased database corruption risk in a multiple-user environment.
Jet and Access Bugs
Over time, evidence has suggested that some form of corruption can be directly related to software bugs. For example, ‘Name Auto-Correct’ should always be disabled during a new database start up since the feature’s infamous bug is know to increase the risk of corrupt data. In this case, consequences outweight the benefits especially where data corruption is concerned.
Database Design Inefficiencies
A database design format used for multiple-users file level access, Access (and its corresponding Jet Database) is known to cause severe file size bloating. This puts tremendous pressure on network protocol while significantly increasing data corruption risk.
Multiple-user databases having a split front-end Graphical User Interface and a separate back-end database can provide network performance improvements and less risk of corruption than using a combined database.
The world’s only Microsoft Access Enterprise Support Service Offering!
