Corruption Issues

When Access is not used properly, database corruption can often occur which results in an unusable 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 an error occurs in the underlying binary file format of the database.

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 in the database without informing the user. Minor database corruption updates are quite common and most of the time you will never 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 be of service to you.

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 the following functions: 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 causes Database Corruption?

There are a number of factors that can lead to database corruption. A few are outlined below.

Hardware Failures

Since networking equipment and hard drives are the most common hardware issues that affects database integrity, your hardware should be regularly tested to prevent database corruption. Network cards can lose packets and 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. The underlying Jet Database is extremely sensitive to the application crash because Jet works at a file-system based level. As a result, if your Access application crashes, the Jet Database also immediately terminates any writing without finishing. This does not occur in a client-server based model such as SQL Server.

Plug-In Software Conflicts

If you have 3rd party plug-ins for Access (including 3rd party ActiveX controls) installed on your computer that have not been designed properly, using them in your Access database can result in database corruption. It is important to keep in mind that these 3rd party plug-ins are also programs. Therefore, they often run in the same application memory space that Microsoft Access does. As a result, if the 3rd party plug-in crashes, so will Microsoft Access.

JET Software Conflicts

Over the years, the Jet Database Engine has undergone four major release version updates in addition to a variety of service pack changes. If one database is shared between multiple users who have different Jet service packs and/or updates installed, there is a potential that database corruption will occur. Specifically, this is caused by the minor differences in the mdb file format and the file locking mechanisms. The latest version of the Jet Database has greatly reduced the possibility of database corruption; however the potential for corruption still exists.

Multi-User Access

The Microsoft Access Jet Database Engine is file-based (unlike SQL Server which is client-server based). As a result, the Jet Database uses a Lock file (.ldb) to synchronize database read/writes which allows multiple users to share the same file over a network. This Lock file relies on Windows OS API calls to handle the file synchronization which in turn relies on file-transfer networking protocols. It is important to note that all network connections have a bandwidth limit. Therefore, any other software on your machine transferring data over the same network socket could potentially slow down the Jet communication. When this slow down occurs, the connection often disconnects due to a 'time-out' failure. As a result, the database is left in a 'suspect' state and often needs repairing 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 function the user was performing when the problem occurred. For example, if the user was attempting to append a large SQL query when the network connection was lost, most likely the database will be corrupt.

TIP: If you have a file that is stored on a Windows NT based file server, you may want to consider disabling 'opportunistic locking' (oplocks) on the file server. While opportunistic locking does improves file I/O performance, it also increases the risk of database corruption in a multi-user environment.

Access and Jet Bugs

Over time, evidence has suggested that some form of corruption can be directly related to bugs in the software. Microsoft will probably always deny this, however the 'Name Auto-Correct' feature of Access is the most well known bug that causes all sorts of problems and increases the chances of data corruption. This feature should always be disabled when starting a new database. In this case, the benefits of this feature do not outweigh the consequences especially where data corruption is concerned.

Inefficient Database Design

As a result of a database format that is designed for multi-user access at the file level, Access (and its corresponding Jet Database) is known for causing severe file size bloating. This extra bloating puts a tremendous amount of pressure on any network protocol and significantly increases the risk of data corruption.

Multi-user databases with a split front end GUI (Graphical User Interface) and a separate back end database can provide improved network performance and less corruption risk than having one combined database for both.

By Admin at 16 Feb 2016, 17:30 PM



Post a comment

Please correct the following: