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
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.
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
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
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
- 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
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.
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
Recovering a Damaged Database
The following steps outline a general method that you can use for
repairing a damaged database:
- Install the latest Microsoft Jet service pack.
This makes sure that you have the latest bug fixes to Microsoft Jet.
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 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
How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
- Make a copy of the damaged database (.mdb) file as a
- 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:
The .ldb file contains a list of users who have the
- when the last user does not have delete permissions to
the folder that contains the .mdb file
- when the database is corrupted
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.
information about how to do this and example code, click the article number
below to view the article in the Microsoft Knowledge Base:
HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000
- Run the Compact and Repair utility as follows:
- If the database is open, close it.
- On the Tools menu, point to Database Utilities, and then click Compact and Repair
- In the Compact Database From dialog box, click the file that you want to compact, and then
- 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.
- 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
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:
- In the Database window, click Pages under Objects.
- Click the New button.
- In the New Data Access Page dialog box, click Existing Web page, and then
- If the damage is in a table, and the previous steps do not
recover the table, try the following:
- 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.
- Delete any relationships associated with this table,
and then delete the table from the database.
- Compact the database.
- Re-create the table and any relationships the table
- 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.
- 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.
- Reenter any records that you were forced to
- 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
- 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.
- 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.
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:
ACC: Microsoft Access Newsgroups Available on the Internet
Typical Causes of .mdb File Corruption
There are four main causes of corruption in Access/Jet .mdb
Database Is Suspect/Corrupted Because of Interrupted Write Operation
Correct shut down that is completed by clicking Exit
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:
ACC2000: Database Password Appears Even Though It Was Never Set
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
HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000
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
- 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
- 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:
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.
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:
ACC2000: Jet Compact Utility Available in Download Center
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:
ACC97: How to Repair a Damaged Jet 3.5 Database
ACC: How to Troubleshoot/Repair Damaged Jet 3.0 and Prior Databases