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.

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

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.

3) 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 of 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:

M_Contacts

Contact_ID	Contact_Name	Contact_Address	Contact_City	Contact_State	Contact_Phone
1	Joseph Dean	148 Kirwans Landing Lane	Chester	MD	410 929-9399
#Deleted	#Deleted	#Deleted	#Deleted	#Deleted	#Deleted
3	Billy Bob	12 Main Street	Somewhere	XX	555-1212

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

Here is the simple 4-step process we use the recover all records except the corrupted record:

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
FROM M_Contacts;

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

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 predicate:

INSERT INTO M_Contacts_Fixed
SELECT
FROM M_Contacts
WHERE M_Contacts.Contact_ID<=1;

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 the table:

INSERT INTO M_Contacts_Fixed
SELECT
FROM M_Contacts
WHERE M_Contacts.Contact_ID>=3;

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 table appropriately.

If you don't feel likely tackling this yourself then give us a call and we can usually fix the table over the Internet in less than 1 hour ($150).