ACC: How to Repair a Damaged Database

Last reviewed: January 6, 1998
Article ID: Q109953
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

Environmental factors, such as power outages and fluctuations, improper closing of Microsoft Access, and disk or network failures may leave your database file damaged. The Repair and Compact utilities in Microsoft Access are useful tools for recovering and optimizing Microsoft Access database files. This article describes how to use these utilities and additional alternatives for repairing a damaged database file.

MORE INFORMATION

Although the steps outlined in this article are usually successful at recovering damaged database files, you should always have a recent backup copy of your database file on hand.

The Repair Utility

You can use the Repair utility to fix databases with certain types of damage. The Repair utility attempts to repair only the tables, queries, and indexes in the database. It does not attempt to repair damaged forms, reports, macros, or modules; it does, however, copy them to the new, repaired database.

NOTE: Run the Repair Database command only when the Microsoft Jet database engine returns an error message indicating that the Repair should be run. The Repair Database command should not be run under any other circumstances.

Compact Utility

The Compact utility eliminates empty space in an existing database. It does so by creating a new destination database and copying each object in the old database to the new one. If you choose to compact the database into the original database name instead of a new database, the Compact utility creates a temporary database, exports all the objects from the original database into the temporary database, removes the original database, and renames the temporary database to the original database's name.

Recovering a Damaged Database

The following steps outline a general method for repairing a damaged database:

  1. If you are using Microsoft Access version 2.0, obtain the Microsoft Access 2.0 Service Pack if you are getting either of the following error messages in a multiuser environment:

        - Couldn't open SYSTEM.MDA
    

          -or-
    

        - <Database> is corrupt or is not a database file. Attempt to repair?
    

    In addition to fixing the problem mentioned above, the Service Pack also includes an enhanced Repair utility. For more information about the Service Pack, please see the following articles here in the Microsoft Knowledge Base:

          ARTICLE-ID: Q123589
    
          TITLE:      ACC2: Error Message "Couldn't Open SYSTEM.MDA"
    
          ARTICLE-ID: Q123823
          TITLE:      ACC2: MS Access Version 2.0 Service Pack Questions and
                      Answers
    
          ARTICLE-ID: Q123588
          TITLE:      ACC2: Microsoft Access Repair Utility Enhanced
    
    

  2. Back up the damaged database (.mdb) file.

  3. Delete the .ldb file if it is present. The corresponding .mdb file should be closed before deleting this 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 (for shared use) the Northwind.mdb sample database in the c:\Msoffice\Access folder, then a file called Northwind.ldb is automatically created in the same folder. In Microsoft Access 7.0 and 97, the .ldb file is automatically deleted after the last user has exited the database with two exceptions -- when the last user does not have delete privileges to the folder containing the .mdb file or when the database is corrupted.

    For more information about .ldb files, please see the following articles here in the Microsoft Knowledge Base:

          ARTICLE-ID: Q136128
    
          TITLE:      ACC: Introduction to .ldb Files (95/97)
    
          ARTICLE-ID: Q109957
          TITLE:      ACC: Introduction to .ldb Files (1.x, 2.0)
    
    

  4. Run the Compact utility as follows:

        a. If a database is open, close it.
    

        b. Point to Database Utilities on the Tools menu, and then click
           Compact Database.
    

        In Microsoft Access 2.0 or earlier, on the File menu, click Compact
        Database.
    

    At this point, check your database to see if the damage has been repaired. If not, continue with the remaining steps.

  5. If the damage is in a table, query, or index, run the Repair utility as follows:

        a. If a database is open, close it.
    

        b. Point to Database Utilities on the Tools menu, and then click Repair
           Database.
    

        In Microsoft Access 2.0 or earlier, on the File menu, click Repair
        Database.
    

        c. Select the damaged file in the Repair Database dialog box, and then
           click Repair.
    

        In Microsoft Access 2.0 or earlier, enter the name of the damaged
        file in the File Name box, and then click OK.
    

        If the repair is successful, you will receive the following message:
    

        In Microsoft Access 97:
    

             Successfully repaired the '<path><database name>' database.
    
        In Microsoft Access for Windows 95:
    
             Microsoft Access repaired database '<path><database name>'
             successfully.
    
        In Microsoft Access 2.0 or earlier:
    
             Repair of database '<path><database name>' completed successfully.
    
       If the repair does not succeed, you will receive a message stating so.
       This means the damage to a table, query, or index is so severe that it
       cannot be corrected, or the damage is in an object that the Repair
       utility cannot affect.
    
    

  6. If the damage is in a table, and the previous steps have not recovered it, try the following:

        a. From within Microsoft Access, export the table to an ASCII
           (delimited text) file. For more information about this topic, search
           on the phrase "delimited text," and then view the "exporting Access
           data" topic using the Microsoft Access Help Index.
    

        b. Delete any relationships associated with this table, and then delete
           the table from the database.
    

        c. Compact the database.
    

        d. Recreate the table and any relationships it had.
    

        e. 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
    

        f. Re-import the ASCII file into the newly recreated table. For more
           information about this topic, search on the phrase "delimited text,"
           and then view the "importing or linking" topic using the Microsoft
           Access Help Index.
    

        g. Recreate any records you were forced to delete.
    

  7. 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 recreate the relationships. This technique resolves problems with damaged system tables in the database.

  8. If the damage is in a form or report, the damage can be either in the form or report itself or in one or more controls on the form or 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 report, create a new
          form or report, and then recreate the controls on the new form or
          report. It is best to recreate all the controls, because there is no
          way to tell which controls are damaged.
    

  9. If the damage is in a macro or module, the damage can be in the macro or module itself or in the contents of the macro or module. You can delete the macro or 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 recreate 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 more information about the Microsoft Access Internet newsgroups, please see the following article here in the Microsoft Knowledge Base:

   ARTICLE-ID: Q150057
   TITLE:      ACC: Microsoft Access Newsgroups Available on the
               Internet

For information on how to obtain Microsoft Jet 3.51, please see the the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172733
   TITLE:      ACC97: Updated Version of Microsoft Jet 3.5 Available on MSL

REFERENCES

For more information about repairing databases, type "repairing" in the Office Assistant, click Search, and then click to view "Repair a damaged database."


Additional query words: corrupt corruption
Keywords : GnlCmp kbusage
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 6, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.