databasedev.co.uk - database solutions and downloads for microsoft access

Database Corruption and Prevention

A corrupt database is a database that has lost some of its data or functionality. The corruption may be the result of several factors, to include:

• Too many users for the processing capability of the computer.
• Poor structuring of the software that interfaces with the database.
• Power outages.
• Interruptions or crashes during the writing of data.
• Not installing the proper or latest updates and services packs for the operating system and database software.
• Multiple users attempting to access and modify the same data at the same time.

A Microsoft Access database has several components, which include the data as well as database objects. Database objects include tables, queries, reports, forms, macros and modules. The effects of database corruption include the generation of errors during processing; deleted record fields; and the denial of access to the database, its objects or its data. Some common indications of a corrupt database include error messages when:

• Opening and closing databases
• Running reports, forms or queries
• Updating records
• Scrolling through data records

The default database engine underlying Microsoft Access is known as the Jet Engine. Using the appropriate Jet database, which corresponds to your version of the Access Jet Engine helps to maintain the stability of your databases. Each version of the Jet Engine uses a different method of writing to databases. Jet Engines are not backwards compatible so that older Jet Engines cannot perform read and write operations on newer Jet database file formats. The particular Jet database to be used with your version of Microsoft Access is as follows:

Microsoft Access Version	Jet Engine	Recommended Jet Database
Access 2.0	Jet 2.0	Jet 2.0
Access 95	Jet 3.0	Jet 3.0
Access 97	Jet 3.5	Jet 3.0
Access 2000	Jet 4.0	Jet 4.0
Access 2002	Jet 4.0	Jet 4.0

The data management functions, performed by Microsoft Access utilize Microsoft’s Visual Basic programming system. The Microsoft Access database can be run as a standalone database or as a back-end database server to a network of multiple users. Because Microsoft Access is a Visual Basic based application, all necessary components of Visual Basic must be loaded in order that databases operate correctly. If your computer system cannot properly load Visual Basic modules into memory, your database files may become corrupt.

Poor database design can also lead to corrupt databases. A database that efficiently utilizes your computer resources and efficiently manages the manipulation of data, within the database, reduces the chance of errors.

Using reserved words and reserved characters when naming database objects and fields leads to database corruption. Using reserved words and characters in their reserved syntax or in conjunction with other words or characters may confuse the software in its attempt to pass information between database objects and perform other database functions.

Failure to periodically compact your database leads to fragmented data and indexes, unreliable table statistics and unpredictable queries, which may corrupt your database. The Microsoft Access compact utility eliminates wasted space from memory blocks that were created by deleting and modifying table records and objects. The utility restructures table records and objects, then stores them in successive memory blocks, which improves read and write operations. The utility also updates table statistics to reflect database characteristics of the restructured data. Database queries are then optimized using the updated statistics, which leads to queries that are more efficient.

Installing Microsoft Access to perform server-like functions can lead to corruption. Microsoft Access utilizes a file-sharing database system at the client level. Microsoft Access is not designed to scale for multi-user network environments provided by client-server database systems. Multiple attempts to perform read, write and locking operations may prevent these operations from completing successfully and corrupt the database. Also multiple users may exceed the capacity of shared resources and cause interruptions or complete shutdowns of the computer system.

Maintaining up-to-date service packs ensures that the latest bugs and fixes for opportunistic locking (oplocks) and other features are being used with your installation. Improper installations of service packs to handle problems associated with oplocks are cause for corruption. For those databases that allow oplocks, disabling the oplock feature is an option, but it may also have undesirable effects on other applications.

Your database maintenance should include routine backups for the recovery of all or parts of your database, which cannot be recovered by other means. In addition, you should routinely test your chosen backup procedure, and the resultant files, to ensure that they effectively copy the database in its entirety.

Any processes or procedures that cause interruptions or power losses while data is being written to the database, can leave your database in a suspect state. This includes interruptions from your network, shutdowns while the Jet database is still open and using the task manager to close database operations.

---

If you are experiencing problems with Microsoft Access database corruption, and need to recover your Microsoft Access data, it may still be repairable.

databasedev.co.uk recommends Access Database Repair and Recovery Software from Cimaware.com

AccessFIX is an Microsoft Access database repair and recovery Software that restores damaged MS Access files, rebuilding the table data. This utility works with MS Access 95, 97, 2000, XP, and 2003

Restore your database files easily in only 15 minutes:

Download the free database repair and recovery software demo now to evaluate the recovery capabilities of the program. Once AccessFIX demo is installed you will easily recover your database file and view the results with only a few clicks.