Knowledge Base

How to troubleshoot and to repair a damaged Access 2002 or later database

Article ID: 283849

Article Last Modified on 5/7/2007

APPLIES TO

Microsoft Office Access 2003
Microsoft Access 2002 Standard Edition

This article was previously published under Q283849

Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 97 version of this article, see 279334.

For a Microsoft Access 2000 version of this article, see 209137.

SUMMARY

There are several things that can happen, both inside and outside of Access, that may leave your database file damaged (corrupted). The symptoms of a corrupted database can range from #Deleted appearing in certain records, to you being unable to open one of the objects in the database, to you being unable to open the database file in Access altogether. The Compact and Repair utility in Microsoft Access is a useful tool for trying to optimize or trying to repair Microsoft Access database files. This article describes this Access utility and offers additional alternatives for trying to repair damaged databases. This article also provides information about what can cause databases to become damaged.

Note Microsoft Jet, the database engine that is used in Microsoft Access, is a file sharing database system. When Microsoft Jet is used in a multi-user environment, multiple client processes are using file read, write, and locking operations on a shared database. Because multiple client processes are reading and writing to the same database and because Jet does not use a transaction log (as do the more advanced database systems, such as SQL Server), it is not possible to reliably prevent any and all database corruption. If you need a database system to run 24 hours a day, seven days a week in a multi-user environment, Microsoft recommends that you use a true client/server database system that supports durable transactions, such as Microsoft SQL Server.

MORE INFORMATION

Although the steps that are outlined in this article are usually successful at recovering damaged database files, to safeguard your data, Microsoft recommends that you create a backup copy of your database file as often as you can.

Description of the Compact and Repair Utility

Compacting a database

When you compact a database, the compact process will reclaim unused space in a database that is created by object and record deletions. It does so by creating a new destination database and by copying each object in the old database to the new database. If you choose to compact the database into the original database name instead of to a new database, the compact process creates a temporary database, exports all the objects from the original database into the temporary database, removes the original database, and then renames the temporary database to the name of the original database.

The following is a list of actions that the compact process performs:

It reorganizes the pages of a table so that they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database.
It reclaims unused space that is created by object and record deletions. When objects or records are deleted from the database, the space they occupied is marked as available for new additions to the database. However, the size of the database never shrinks unless you compact the database. For databases in which objects and records are frequently added, deleted, and updated, you should compact frequently.
It resets incrementing AutoNumber fields so that the next value allocated will be one more than the highest value in the remaining records. For example, if all the records in the database have been deleted, after you compact the database, the value in the AutoNumber field will be 1 when the next record is added. If the highest remaining AutoNumber value in the database is 50, after you compact the database, the value in the AutoNumber field will be 51 when the next record is added. Note that this is true even if records containing values higher than 50 were added previously but were deleted prior to compacting.
It regenerates the table statistics that are used in the query optimization process. These statistics can become out-of-date over time. This typically happens if transactions are rolled back or if the database is not properly closed because of an unexpected power loss or because the computer is turned off before a program that is using Microsoft Jet has had a change to quit completely.
It flags all queries so that they will be recompiled the next time that the query is run. This is important because database statistics can change and a previously compiled query may have an inaccurate query plan.

Repairing a database

The repair process tries to repair only the tables, the queries, and the indexes in the database. It does not try to repair damaged forms, reports, macros, or modules.

What to Make Sure of Before You Run the Compact and Repair Utility

Before you run the Compact and Repair utility on a database, make sure of the following:

Make sure that you have enough free storage space on your hard disk for both the original and the compacted versions of the Access database. This means that you must have enough free storage space for at least twice the size of your Access database on that drive. If you need to free some space, delete any unneeded files from that drive, or, if possible, move the Access database to a drive that has more free space.
Make sure that you have both Open/Run and Open Exclusive permissions for the Access database. If you own the database, make sure to set these permissions. If you do not own the database, contact its owner to find out if you can get these permissions.
Make sure that no user has the Access database open.
Make sure that the Access database is not located on a read-only network share or has its file attribute set to Read-only.

Steps for trying to repair a damaged database

The following steps outline a general method that you can use to try to repair a damaged database:

Make a copy of the damaged database (.mdb) file so that you have a backup.
Delete the .ldb file if it is present. You must close the corresponding .mdb file before you delete the .ldb file.

The .ldb file is used to determine which records are locked in a shared database and by whom. If a database is opened for shared use, the .ldb file is created with the same name as the corresponding database (.mdb). For example, if you open the Northwind.mdb sample database for shared use in the C:\Msoffice\Access folder, a file named Northwind.ldb is automatically created in the same folder. The .ldb file is automatically deleted after the last user has exited the database with these two exceptions -- when the last user does not have delete permissions to the folder containing the .mdb file or when the database is corrupted. The .ldb file contains a list of users who have the database open.
Run the Compact and Repair utility. To do so, follow these steps:
1. If the database is open, close it.
2. On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.
3. In the Database to Compact From dialog box, select the file that you want to compact, and then click Compact.
4. In the Compact Database Into dialog box, enter a new file name, and then click Save.
  
  If the compact and repair does not succeed, you will receive a message stating so. This means the damage is so severe that it cannot be corrected.
If the previous steps fail to recover your damaged database, try creating a new database and importing the objects, one-by-one, from the old database to the new one. Then re-create the relationships. This technique resolves problems with damaged system tables in the database.

Note You cannot import data access pages with the Import Wizard. Instead, open an existing data access page in the new database. To do so, follow these steps:
1. In the Database window, click Pages under Objects.
2. Click New.
3. In the New Data Access Page dialog box, click Existing Web page, and then click OK.
4. In the Locate Web Page window, browse to the location of the data access page.
If the damage is in a table, and the previous steps have not recovered the table, try the following:
1. In Microsoft Access, export the table to an ASCII (delimited text) file. For more information view the topic "Export data or database objects." in Microsoft Access Help.
2. Delete any relationships associated with this table, and then delete the table from the database.
3. Compact the database.
4. Re-create the table and any relationships it had.
5. Using a word processor, examine the ASCII file for bad or strange data and remove those records. Save the file in an ASCII text file format.
6. Re-import the ASCII file into the newly re-created table. For more information about this topic, search the Microsoft Access Help Index for the word "delimit" and then view the topic "Import or link data and objects."
7. Re-enter any records that you were forced to delete.
If the damage is in a form or a report, the damage can be either in the form or the report itself or in one or more controls on the form or the report. You can delete the form or report and import it from the backup copy of your database or use one of the following options:
- If the damage is in the form or report itself, create a new form or report, and then copy the controls from the original form or report.
- If the damage is in a control on the form or the report, create a new form or report, and then re-create the controls on the new form or report. It is best to re-create all the controls, because there is no way to tell which controls are damaged.
If the damage is in a macro or a module, the damage can be in the macro or the module itself or in the contents of the macro or the module. You can delete the macro or the module and import it from the backup copy of your database or use one of the following options:
- If the damage is in the macro or module itself, create a new macro or module, and then copy the contents of the original macro or module.
- The damage could involve non-ASCII characters embedded in the module. Save the module as a text file, remove any bad or strange data, and then reload the text file into a new module.
- If the damage is in the contents of the macro or module, you must create a new macro or module, and then re-create the contents of the original macro or module.

If you cannot repair the database with these steps, the database is probably damaged beyond repair. If this is the case, you should restore your last backup database or re-create the database.

As a final alternative, some consultants may provide a Microsoft Access database repair service. Because this is such a specialized service, the most efficient way to locate a consultant is to post a message in the Microsoft Access "Third Party and User Groups" Internet newsgroup, which has the following newsgroup address:

microsoft.public.access.3rdpartyusrgrp

For additional information about Microsoft Access Internet newsgroups, please see the following article in the Microsoft Knowledge Base: For more information, click the following article number to view the article in the Microsoft Knowledge Base:

287756 AutoNumber field is not reset after you compact a database

Why .mdb files may become corrupted

There are three main reasons why an .mdb file may become corrupted, as follows:

Interrupted write operation
Faulty networking hardware
Opening and saving the .mdb file in another program

Interrupted write operation

You should always properly quit Access by clicking Exit or Close on the File menu. If a database is open and writing data when Access is abnormally shut down, the Jet database engine may mark the database as suspect/corrupted. This can happen if you manually turn off the computer without first quitting Windows or if you lose power. Other situations can occur that do not shut down Access but that may still interfere with Jet writing data to the disk while the database is open. This can happen, for example, when networks experience data collisions or when disk drives malfunction. If any of these interruptions occur, Jet may mark the database as potentially corrupted.

When Jet begins a write operation, it sets a flag, and it then resets the flag when the operation is complete. If a write operation is interrupted, the flag remains set. When you try to open that database again, Jet determines that the flag is set and reports that the database is corrupted. In most cases, the data in the database is not actually corrupted, but the set flag alerts Jet that corruption may have occurred. In cases such as this, compacting or repairing the database (or both) can typically restore the database. Fortunately, there are ways to determine which user and workstation was responsible for marking the file as suspect. With Microsoft Visual Basic for Applications in Access, you can output a list of users who are logged into a specific database.

208449 Microsoft Access newsgroups available on the Internet

For more information about how to do this, see the "How to determine which users/workstations are causing the file to be marked suspect" section later in this article.

Faulty networking hardware

Sometimes corruption can occur without the Jet database engine being involved. For example, faulty networking hardware can cause a file to become corrupted. The cause can be one or more links in the hardware chain between the computer that the database resides on and the computer that has the database open. This list includes, but is not limited to, network interface cards, network cabling, routers, and hubs.

Hardware-based corruption is typically indicated by .mdb files that cannot be restored through the use of compacting, repairing, or Jetcomp. Hardware corruption will typically recur until the responsible hardware is repaired or replaced.

Opening and saving the .mdb file in another program

There is no way to recover an .mdb file that was opened and then saved in a different program. For example, you could open and save an .mdb file in Microsoft Word, but if you were to do so, the .mdb file could never be recovered, except from a backup copy. If you accidentally open an .mdb file in another application, be sure not to save it. It really serves no purpose to open an .mdb file in another application because if you do, all you see is a seemingly random series of characters.

How to determine which users/workstations are causing the file to be marked as suspect

When you troubleshoot to determine what is causing database corruption, you may have to see who is logged into the database. With Microsoft Visual Basic for Applications in Access 2002 or in Access 2003, you can access a list of users who are logged into a specific database.
For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:

285822 How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2002 or in Access 2003

Steps that you can take to prevent corruption

To prevent database corruption:

Avoid losing power during database writes. Losing power during database writes can cause the database to be left in a suspect state.
Avoid dropping network connections.
Avoid abnormal termination of Microsoft Jet connections such as power loss, manual shutdown, having Task Manager shutdown the application, and so on.
When you are programming, close all DAO and ADO objects that you have open. Examples include Recordset, QueryDef, TableDef, and Database objects.
Fatal system errors almost always cause abnormal termination. If your database is prone to fatal errors, you should resolve the errors before the database becomes too damaged to open or to recover. For more information about how to do this and sample code that you can use, click the following article number to view the article in the Microsoft Knowledge Base:

285822 How to determine who is logged onto a database by using Microsoft Jet UserRoster in Access 2002

For more information about fatal system errors, click the following article numbers to view the articles in the Microsoft Knowledge Base:

294301 ACC2002: How to troubleshoot fatal system errors in Access 2002 running on Microsoft Windows 2000
Compact the database often.
Do not run IPX on Microsoft Windows NT Server where Jet databases are located across the network and the client is Microsoft Windows 95 with IPX/SPX. Instead run TCP-IP on the Windows NT Server and a dual protocol stack of IPX and TCP-IP on the Win95 client. (Windows NT to Windows NT with IPX/SPX will not cause the problem, nor will Novell to any client.)
Avoid a large number of open and close operations in a loop (more than 40,000 successive open and close operations could cause corruption).

Special note on converted databases

In versions of Access earlier than Access 2002, if there were errors while you were converting a database to the current version, there was no easy way to determine which objects were affected and possibly contained noticeable corruption.

When Microsoft Access 2002 or later encounters errors while converting an Access file, you can view a summary of these errors by opening the Conversion Errors table in the new Access file. The Conversion Errors table contains the following columns:

Object Type. The type of database object in which Access encountered an error, or "Database" if Access encountered an error that is not specific to a particular type of object.

Object Name. The name of the object in which Access encountered an error. If Access encounters a compilation error during conversion, however, the name of the module that contains the error is not specified.

Error Description: If necessary, you can press SHIFT+F2 to view the entire description of the error.

REFERENCES

For more information about repairing databases, click Microsoft Access Help on the Help menu, type repairing in the Office Assistant or the Answer Wizard, and then click Search to view the topic. For additional information about troubleshooting databases in earlier versions of Access, click the following article numbers to view the articles in the Microsoft Knowledge Base:

209137 How to troubleshoot and repair a damaged Jet 4.0 database

279334 How to repair a damaged Jet 3.5 database

109953 How to troubleshoot/repair damaged Jet 3.0 and prior databases

284152 How to troubleshoot fatal system errors in Access 2002 running on Windows Millennium

Additional query words: OfficeKBHowTo corrupt corruption inf ACC2002 ACC2003 ReviewDocId

Keywords: kbcorrupt kbtshoot kbhowto KB283849

Microsoft KB Archive/283849

Contents