How to troubleshoot and repair a damaged Jet 4.0 database in Access 2000

Environmental factors may leave your database file damaged. The symptoms of a corrupted database can range from "#Deleted" appearing in certain records, to the inability to open one of the objects in the database, to the complete inability to open the file in Access. The Compact and Repair utility in Microsoft Access is a useful tool that you can use to recover and to optimize Microsoft Access 2000 database files. This article describes this Access 2000 utility and then offers other ways to repair damaged databases. Also, information about what can cause corruption is provided.

Although the steps outlined in this article are typically successful for recovering damaged database files, to safeguard your data you must create a backup copy of your database file as frequently as you can.

The Compact and Repair Utility

The Compact utility gets rid of empty space in an existing database. The Compact utility does this by creating a new destination database and then by copying each object in the old database to the new database. If you select to compact the database to the original database name instead of a new database, the Compact utility creates a temporary database, exports all of the objects from the original database to the temporary database, removes the original database, and then renames the temporary database to the name of the original database.

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

The following is a list of actions the Compact utility performs:

• Reorganizes the pages of the table so that the pages reside in adjacent database pages.
  
  This improves performance because the table is no longer fragmented across the database.
• Reclaims unused space that is created by object and by record deletions.
  
  When objects or records are deleted from the database, the space that they occupy is marked as available for new additions to the database. However, the size of the database never shrinks unless the database is compacted. For databases in which objects and records are frequently added, deleted, and updated, you must compact frequently.
• Resets incrementing AutoNumber fields so that the next value allocated is one more than the highest value in the remaining records.
  
  For example, if all records in the database are deleted after you compact the database, the value in the AutoNumber field is 1 when the next record is added. If the highest remaining AutoNumber value in the database is 50 after you compact, the value is then 51 when the next record is added. Note that this is true even if records that contain values higher than 50 were added previously but were deleted before compacting.
• Regenerates the table statistics that are used in the query optimization process.
  
  These statistics can become out-of-date over time. This typically occurs if transactions are rolled back, or if the database is not properly closed because of a power loss or a failure to completely exit the program by using Microsoft Jet before you turn off the computer.
• Flags all queries so that they are recompiled the next time that the query is executed.
  
  This is important because database statistics can change, and a previously compiled query may have an inaccurate query plan.

Important Guidelines for Running the Compact and Repair Utility

Before you can run the Compact and Repair Utility on a database, the following conditions must be met:

• You must logon with an account that has Modify Design or Administer security permissions for all tables in the database.
• Enough disk space must exist for both the original and the compacted versions of the database, even if the database is compacted through the Microsoft Access 2000 user interface by using the same file name.
  
  The repaired or compacted database is renamed as the original database only when the Compact utility is successful.
• Other users must not have the database open.
  
  When a database is compacted, the database must be opened exclusively by Microsoft Jet to prevent any users from accessing and from modifying the database during the process.

Recovering a Damaged Database

The following steps outline a general method that you can use for repairing a damaged database:

1. Install the latest Microsoft Jet service pack.
   
   This makes sure that you have the latest bug fixes to Microsoft Jet. For additional information about how to download the latest Jet 4.0 Service Pack, click the following article number to view the article in the Microsoft Knowledge Base:
   239114  (http://support.microsoft.com/kb/239114/ ) How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
   If you work in a network environment, you must install the latest Microsoft Jet service pack on all client computers. You do not have to install the Microsoft Jet service pack on the network file server unless the network file server also runs one or more applications that use Microsoft Jet.
2. Make a copy of the damaged database (.mdb) file as a backup.
3. Delete the .ldb file if it is present. You must close the corresponding .mdb file before you delete this file.
   
   The .ldb file is used to determine which records are locked in a shared database and by whom the records are locked. If a database is open for shared use, the .ldb file is created with the same name as the corresponding database (.mdb). For example, if you open (for shared use) the Northwind.mdb sample database in the C:\Msoffice\Access folder, a file called Northwind.ldb is automatically created in the same folder. The .ldb file is automatically deleted after the last user exits the database, with two exceptions:
   
   
   • when the last user does not have delete permissions to the folder that contains the .mdb file
   • when the database is corrupted
   The .ldb file contains a list of users who have the database open.
   
   When you troubleshoot to find out what is causing database corruption, in some cases you may have to see who is logged into the database. With Microsoft Visual Basic for Applications in Access 2000, you can output a list of users who are logged into a specific database. For additional information about how to do this and example code, click the article number below to view the article in the Microsoft Knowledge Base:
   198755  (http://support.microsoft.com/kb/198755/EN-US/ ) HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000
4. Run the Compact and Repair utility as follows:
   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 Compact Database From dialog box, click 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 receive a message stating so. This means the damage is so severe that the damage cannot be corrected.
5. If the previous steps fail to recover your damaged database, try to create a new database and then import the objects, one-by-one, from the old database to the new database. Then re-create the relationships. This technique resolves problems with damaged system tables in the database.
   
   Note that you cannot import data access pages with the Import Wizard. Instead, you must open an existing data access page in the new database. To do this, follow these steps:
   1. In the Database window, click Pages under Objects.
   2. Click the New button.
   3. In the New Data Access Page dialog box, click Existing Web page, and then click OK.
6. If the damage is in a table, and the previous steps do not recover the table, try the following:
   
   
   1. In Microsoft Access 2000, export the table to an ASCII (delimited text) file. For more information about this topic, you can search on the phrase "delimited text," and then you can view the "exporting Access data" topic by using the Microsoft Access Help Index.
   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 the table had.
   5. If you use a word processor, you can examine the ASCII file for bad or for strange data and then remove those records. Save the file in an ASCII text-file format.
   6. Reimport the ASCII file to the newly re-created table. For more information about this topic, you can search on the phrase "delimited text," and then you can view the "importing or linking" topic by using the Microsoft Access Help Index.
   7. Reenter any records that you were forced to delete.
7. If the damage is in a form or in 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 the report and then import it from the backup copy of your database, or you can use one of the following options:
   • If the damage is in the form or the report itself, you can create a new form or report, and then you can copy the controls from the original form or report.
   • If the damage is in a control on the form or the report, you can create a new form or report, and then re-create the controls on the new form or report. You must re-create all the controls, because there is no way to tell which controls are damaged.
8. If the damage is in a macro or in 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 you can use one of the following options:
   • If the damage is in the macro or the module itself, you can create a new macro or module, and then copy the contents of the original macro or module.
   • The damage may involve non-ASCII characters that are 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 of the module, you must create a new macro or new 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 must restore your last backup database or re-create the database.

As a final option, 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: For additional information about Microsoft Access Internet newsgroups, click the article number below to view the article in the Microsoft Knowledge Base:

Typical Causes of .mdb File Corruption

There are four main causes of corruption in Access/Jet .mdb files.

Database Is Suspect/Corrupted Because of Interrupted Write Operation

Correct shut down that is completed by clicking Exit or Close on the File menu, is highly recommended. However, if a database is open and is writing data when Access is incorrectly shut down, the Jet Database Engine may mark the file as suspect/corrupted. This can occur if the computer is manually turned off without first shutting down Windows or if power is lost. Other situations may not shut down Access but may still interfere with the writing of data to the disk by Jet while the database is open. This can occur, for example, when networks experience data collisions or when disk drives malfunction. If any of these interruptions occur, then Jet may mark the database as potentially corrupted.

When Jet begins a write operation, it sets a flag and 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 then 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, if you compact or repair, or do both, you can typically restore the database. Fortunately, there are ways to determine which user and which workstation is responsible for marking the file as suspect. See "How to Determine What Users/Workstations Are Causing the File to be Marked Suspect" topic later in this article to determine which users or workstations, or both, may cause Jet to mark the file as suspect.

Faulty Networking Hardware

In this case, the file corruption does not involve the Jet Engine. Rather, the file is literally corrupted by some outside cause. 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, of repairing, or of Jetcomp. Hardware corruption typically occurs until the responsible hardware is repaired or is replaced.

Opening and Saving the .mdb File in Another Program

There is no way to recover a .mdb file that is opened and then is saved in a different program. For example, Microsoft Word permits you to open an Access database, and then to save it. (No purpose is served if you open a .mdb file in another application because all you see are extended characters.) Saving the file this way causes the .mdb file to prompt you for a database password when you try to open the file in Access. This occurs although the file may have never been password protected in Access. The password prompt occurs in such cases because the first byte range that Access checks when it opens a file is the location where the database password is. If that byte contains corrupted data, Access treats the file as password protected. Even if there was a way to get around the password prompt in this case, the database is still unrecoverable because the binary structure is scrambled and therefore unreadable to Access. You must recover a backup copy of the file as the only solution in this case. For additional information about this issue, click the article number below to view the article in the Microsoft Knowledge Base:

Mismatched Versions of the Jet Database Engine

If you run different versions of the Jet Database Engine in your environment, you can also cause corruption of an Access database. Different versions of Jet write to the database differently, and therefore can be the cause of corruption in a database.

How to Determine What Users/Workstations Are Causing the File to be Marked Suspect

When you troubleshoot to find out what is causing database corruption, in some cases you may have to see who is logged into the database. With Microsoft Visual Basic for Applications in Access 2000, you can output a list of users who are logged into a specific database.

For additional information about how to do this and example code, click the article number below to view the article in the Microsoft Knowledge Base:

Methods That Can Be Used to Prevent Corruption

• Avoid the loss of power during database writes. If power is lost during a database write, this can cause the database to be left in a suspect state.
• Avoid dropping network connections.
• Avoid incorrect termination of Microsoft Jet connections such as power loss, manual shutdown, or allowing Task Manager to shut down the application.
• When programming, close all DAO objects and ADO objects that you have open. Examples include Recordset, QueryDef, TableDef, and Database objects.
  
• Fatal system errors almost always cause incorrect termination. If your database is prone to fatal errors, you must resolve the errors before the database becomes too damaged to open or to recover.For additional information about Fatal System Errors, click the article number below to view the article in the Microsoft Knowledge Base:
  208488  (http://support.microsoft.com/kb/208488/EN-US/ ) ACC2000: How to Troubleshoot Invalid Page Faults in Access 2000
• Compact the database frequently.
• Do not run IPX on a Windows NT-based Server where Jet databases are located across the network and the client is Microsoft Windows 95 with Internet Packet Exchange/Sequenced Packet Exchange (IPX/SPX). Instead, run TCP/IP on the Windows NT-based Server and a dual protocol stack of IPX and TCP/IP on the Win95 client. (Windows NT-to-Windows NT with IPX/SPX does not cause the problem, nor does Novell to any client.)
• Avoid a large number of Open/Close Operations in a loop (40,000 successive open/close operations to over 1,000,000).
• Verify that the latest Microsoft Jet Service Pack is installed on all client machines that access the database file.

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 a downloadable utility that may also repair a damaged database, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about troubleshooting databases in earlier versions of Access, click the article numbers below to view the articles in the Microsoft Knowledge Base: