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

Database Corruption & Repair

A Microsoft Access database may consist of data and indexes as well as the database objects: tables, queries, reports, forms, macros and modules. The best way to protect against loss of data and databases due to corruption is to backup your databases often. Microsoft Access provides a Compact and Repair utility to assist in recovering corrupted databases. However, these utilities have limitations and may not fully recover the database, its objects or its data management functions.

The Compact Utility reduces the physical size of your database by making an exact duplicate of the database while also ridding the database of any excess space created by deleting and modifying data. The deletion of records and the reduction in record sizes, within a database, does not reduce the size of the overall database. Compacting is the only way to reduce the size of databases. The more additions, deletions and modifications performed on databases, the more often they need to be compacted.

The Repair Utility is limited in its capability because it only attempts to repair corrupted tables, queries and indexes. The utility cannot repair forms, reports, modules or macros. These objects can only be recovered from a backup. The Repair Utility performs the following functions:

• De-fragments corrupted tables to improve performance.
• Regenerates table statistics for more efficient queries.
• Flags queries so that they are executed with the newly generated statistics.

A corrupt database may generate error messages, which indicate the cause of the corruption. If errors indicate that the corruption is specific to a database object, you may create a new Microsoft Access database, import the undamaged objects from the corrupted database and restore the corrupted objects from backup.

If errors indicate corruption due to a Visual Basic Application (VBA) or macro, you may recover the affected tables, queries and macros by exporting them from the corrupted database and into a new database. However, you will not be able to export forms, reports and modules. These objects must be recovered from backup.

If errors indicate corruption due to VBA projects, the project stream may exceed the size allowed for your version of Microsoft Access. Installing the appropriate service pack will resolve this problem.

If errors indicate corruption due to the Jet Database or corruption is detected by some other means, the following steps outline processes to restore the corrupted database.

1. Install the latest updates and service packs for your particular version of Microsoft Access and the Jet engine.
2. Create a copy of the corrupted database. Use the copy of the corrupted database for repair attempts. Should you have difficulties in repairing the database you may delete the copy and create a new one.
3. Close the corrupted database, if open.
4. Delete the associated .ldb file, if it exists. The .ldb file only exists for databases with shared users and is used to define access permissions for each user.
5. Run the Compact and Repair Utility to correct the corrupted database.
6. If the Compact and Repair Utility generates errors, the utility is not capable to recover the damaged database. However, you may still be able to recover data from the database tables.
7. Create a new database, import the database objects one-by-one and reestablish relationships between the objects. If the corrupted database is not repaired, move on to Step 8.
8. Attempt to recover data from tables
   1. Create a structure of damaged tables.
   2. If possible, print a copy of the damaged table.
   3. Use the copied structure to create a new table.
   4. Open the damaged table (using the Datasheet View) and the copied table structure.
   5. Copy and Paste the first record from the damaged table to the table structure. If the record contains fields of Data Type, Auto Number, set the Field Size property of the new table to Long Integer and see the note below.
   6. Test the remaining records of the corrupted table for errors.
   7. If no error is produced, follow steps d & e with successive records or groups of successive records.
   8. Upon receiving an error message, do not copy and paste the record, retype the record to the new table.
   9. Continue until all records have been recreated in the new table.
   10. Create a blank database.
   11. Import the new table and any undamaged objects into the new database.
   12. Quit Microsoft Access.
   13. Test the new database for errors.
   14. If no errors are found, rename the corrupted database.
   15. Delete the .ldb file for the corrupted database, if it still exits.
   16. Rename the new database to the name of the corrupted database.

NOTE: If one or more records, of the damaged table, contained fields of Data Type, Auto Number and the Field Size property of the new table was set to Long Integer, you must create, yet, another table to restore the Auto Number values:

• Create a structure of the new table
• Set the Field Size property of the new table back to Auto Number.
• Use an Append query to append records into the structure.

AutoNumber values should be recovered correctly.

If none of the above steps provides a means of recovering your corrupted database and data, you may either seek a third party solution from software vendors that specialize in data recovery or restore your database from backup.

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.