Home  Fees/Services  Microsoft Access Templates  Tutorials  Tutorial Downloads  Articles  Search  Contact  Privacy  Links
Repair Corrupted Tables

 Home > Articles > Microsoft Access Table Repair






Articles:

Disaster Recovery Plan
Form Design Help
MS Access Versus
Replication of Database
Risk Assessment
Selecting a Consultant
Speed Up Access DB
Access on the Internet
ODBC To Corp. DBs
Microsoft Access, Why?
Repair Corrupted Table
Access to QuickBooks
Access Cloud Database

 

Microsoft Office:
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013


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.



We have dozens of downloadable Access small business databases for you to learn and examine.

With our new partnership with Jay McCormick we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.


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.









A Blue Claw Software Design Template:

Contact Management Application
 








A Blue Claw Database Design Article:

Computer Disaster Recovery Planning
 




Contact Information

Copyright 2000-2017 Blue Claw Database Design