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:
- 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
- Back up the damaged database (.mdb) file.
- 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)
- 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.
- 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.
- 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.
- 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.
- 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.
- 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."
|