 Home > Support > Data Recovery Guide > DIY Data Rescue Guide > Data File Repair > 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.
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.
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 databaseEnsure 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 |
To start off, follow these steps:
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:
If the damage occurs in a table, and the previous steps could not recover the error, try the following:
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.
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
