Microsoft Access Table Repair
Repair Corrupted Access Tables
Many of you have come across a table with #deleted or Chinese
characters or square boxes in the fields of the table. Sometimes the
problem will show up as an error message "The Search Key was not found in any
record". You typically will not be able to delete the record nor
modify it. It would be nice to know why this
MS Access table corruption happens and how to fix it. We discuss both issues and
provide a simple method to repair the corrupted database table.
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.
The most common reason for a bad record getting into an Access database table is a bad
network connection or a server that gets locked up for a few seconds.
That's the simple solution and there might be several others. But in the
end the cause usually relates to some delay in an Access front end database communication with the backend database.
The corrupted record problem seems to become more frequent or more likely to
occur in databases that get larger (more records) over time and databases that
are used by several users at one time - naturally these are the more critical
databases out there in the world. Often they run your orders, shipping,
reservations and maintenance records. Since there is more going on with
these databases it is logical to assume the likely of a momentary problem will
To Decrease the Likelihood of Table Corruption
do the Following:
1) Put your backend database on a dedicated
server. Don't put it on a server with other applications or general file
sharing. If you haven't split your Access database into a frontend backend
configuration then that is your first step!
2) Make sure your server has power conditioning (at a
minimum) and ideally it should have a small UPS (uninterruptible power supply).
The same goes for network components and end user computers.
Database tables need to be
constructed for maximum efficiency
in terms of table design. You should avoid memo fields in Access tables.
Avoid tables that look like spreadsheets going out to column ZZ.
Typically, tables designed to
third normal form
will be efficient enough. Appropriate use of primary keys and indexes will
allow Access to perform operations on table efficiently. See our overview
table design recommendations.
4) Compact and repair the backend of the database periodically (monthly).
To Repair a Corrupted Access Database Table Try the Following Procedure:
Here is how a corrupted table record may appear:
12 Main Street
It is likely that you will have thousands of records before the corrupted record
and either none or a few record after the corrupted record.
Whenever you try to use this table you'll get some error message. You will
not be able to make a copy of the table nor will you be able to export it to
Excel. And you won't be able to use Delete Record to simply delete the bad
Here is the simple 4-step process we use the recover all records except the
1) Create a make table query using the Select Top 1 predicate query:
SELECT TOP 1 M_Contacts.Contact_ID, M_Contacts.Contact_Name,
M_Contacts.Contact_Address, M_Contacts.Contact_City, M_Contacts.Contact_State,
M_Contacts.Contact_Phone INTO M_Contacts_Fixed
Note that we are building a new table called M_Contacts_Fixed.
2) Open the newly created table (M_Contacts_Fixed) and delete all records.
Note the datatype of the Contact_ID field. If it was an autonumber field
in the old contact table then it should be an autonumber field in this new
contact table. Our goal will be to retain the original autonumbered field
3) Change the query to an Append Query and add a criteria to the Contact_ID
field so that you select all records above the corrupted record. In our
example we will select all records <=1. Also remove the Select Top 1
INSERT INTO M_Contacts_Fixed
4) Next we will append all the good records after the corrupted record.
Note that you can skip this step if the corrupted record is the last record in
INSERT INTO M_Contacts_Fixed
At this point all the good records will be in the new table (M_Contacts_Fixed ).
All you need to do is delete or rename the corrupted table and rename the new