Recover From Microsoft Access Database Corruption

Related Topics
	Office Startup Error	Word Startup Error	Repair Word Document
	Repair Powerpoint	Prevent Powerpoint Corruption	Excel Startup Error
	Repair Excel Spreadsheet	Repair Access Database	Outlook Express Startup Error
	Repair Outlook DBX Mail	Prevent Outlook Express Corruption	Recovery from Outlook PST Corruption
	How to fix 2GB PST Outlook File Size Limitation

Observations

You're unable to open objects in the database. You notice the "#Deleted" value appearing in certain records of the database.

Possible Causes

The database is corrupted. There are three reasons that can cause a .mdb file corruption.

Interrupted Write Operation

This happens when Microsoft Access is not shut down properly. When the database is open and a data writing process is operational, an abnormal shutdown will cause the Jet database engine to mark the database as potentially corrupted.

Cases of such commonly includes a network data collision and hard disks failure. To understand this, each time Jet commences a write operating, a flag is set to determine the start. When the operation completes, the flag is reseated. During an interruption, the flag remains set during the power off. So when the database powers up, it reports a corruption possibility when it reads the flag.

To combat such problems, the compacting function is used to repair and restore the database. Using Microsoft Visual Basic for Applications in Access, the user and workstation that are responsible for this error can be traced back.

Faulty Networking Hardware

Sometimes a faulty networking hardware or hardware chain can cause a file to be corrupted when it processes the data incorrectly. The accumulated damaged data which is stored in the data gradually corrupts the database. This form of damage can range from mild to a substantially severe corruption depending on the hardware that processes the data.

Typically, such corrupted data is unrecoverable and the faulty networking hardware has to be replaced to resolve the problem.

Opening and Saving the .mdb File in Another Program

This happens when the .mdb file is opened and saved in a different program.

Most people think that a .mdb file contains readable information that is inserted in the database if they should open it with a text editor such as Microsoft Word or WordPad. However, data is written into the file with random series of characters. By saving opening and saving the file this way, it is most certainly that a corruption will occur.

Resolution

The Compact and Repair utility in Microsoft Access is a useful tool to optimizing and repairing Microsoft Access database files.

What Does Compacting Do?
Like the term suggests, compacting reclaims any unused space in a database that is previously occupied due to redundant objects and record deletions. Before you perform a compacting function, the database must be closed. A new database is then created and Jet copies only relevant objects from the old database over to the new.

Amidst the entire process, these are the specific actions that are taking place:

Pages of the tables are reorganized to prevent fragmentation
Unused space marked as available for new additions are cleared
Incrementing auto-number fields are resetted and reorganized
Table statistics used in query optimizations are regenerated
All query are flagged and recompiled
After that happen, the old database is erased and the new database is automatically renamed to the previous.

Repairing a database

Before you attempt to run the Compact and Repair utility on a database, you need to take note of the following:

Ensure that you have sufficient free storage space on your hard disk to contain the original and the compacted versions of the Access database. Generally, you need to have at least thrice the size of your database file of free disk space.

The database must be closed. All users have to log out of the database and external applications that interfere with the database must be terminated

You need to set both full Open/Run and Open Exclusive permissions for the Access database
Ensure that the database is not located on a read-only network share or has its file attribute set to Read-only

Replicate a copy of the damaged database (.mdb) and store it in a removable or external disk

Close Microsoft Access and delete the corresponding .ldb file or the Microsoft Access lock file that is associated with the database

Running the Compact and Repair Utility

To start off, follow these steps:

Open Microsoft Access
Select Tools menu, and then select Database Utilities
Select Compact and Repair Database

Under the Compact Database From dialog box, select the required file to compact, and then click Compact

Under the Compact Database Into dialog box, enter a new file name, and then click Save
- You will be noted if Compacting is unable to process, due to severe database corruption.
- Should compacting fails, try to create a new database and import the objects one-by-one from the old database to the new one. The relationship could be recreated later.

This technique resolves problems with damaged system tables in the database. However, you cannot import data access pages using the Import Wizard. Instead, open an existing data access page in the new database.

To do so, follow these steps:

Under the Database window, click Pages under Objects
Select New
Under the New Data Access Page dialog box, select Existing Web page, and then click OK
In the Locate Web Page window, browse to the location of the data access page

If the damage occurs in a table, and the previous steps could not recover the error, try the following:

Export the table to an ASCII (delimited text) file
Delete any relationships associated with the table, and delete the table from the database
Compact the database
Re-create the table and any relationships

Using Microsoft Word or WordPad, manually examine the ASCII file for bad or strange data and remove those records and then save the file in an ASCII text file format.

Re-import the ASCII file into the newly re-created table
Re-enter any records that were deleted

More Information

If you are unable to repair the database using the above steps, the database is probably damaged beyond repair. If this is the case, you should restore your last backup database. And take note that the compacting repair process only repairs the tables, the queries, and the indexes in the database, leaving damaged forms, reports, macros and modules intact.

This article applies only to

Microsoft Access 2002
Microsoft Access 2003