Home > Data Recovery due to Application Errors > Microsoft Access Database Corruption

Recover from Microsoft Access Database Corruption

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 can not 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