How to Repair MS Access Database Corruption

Author: Steve Childs Reference Number: AA-01671 Created: 2011-05-21 19:01 Last Updated: 2019-05-11 15:57

0 Rating/ Voters

Ways I have fixed CHINESE looking characters that will NOT turn to ######### after Compact

Make a new table (and then copy/paste the necessary table structure) into the new table, or... (after making a backup) try importing the 'structure' only into the database that has the table with the issue...
Sort the records (most likely by the DATE field) to show the chinese looking characters on the top row, then then COPY and PASTE (from the bad table into the good table) 1 or more sections of the good records into the new, clean table.

Start with a Compacted Data File (the one that has a corrupted Table)
Create a new table in Design View (such as Inventory - New Structure)
Open a CLEAN copy of Inventory table in the DataDefs.mdb > Ctrl+A to select all > Ctrl+C to Copy.
Place your cursor in the grey square on the left of the top (empty row) in the new Inventory - New Structure table and press Ctrl+V to paste it.
Open the Indexes and press Ctrl+A to select ALL indexes and press Delete. Then Close Indexes then close the Table and save it.
Make an Append Query starting with Corrupted Inventory table and Append all fields to the new table (BUT...remove the UniqueNumericID... Let that field renumber the records). Then run it.
Then open the new table to see if the same # of records are in the new table.
Then open the new table in design view, then open the Indexes and add Item Number and click the KEY to make it the Primary Key, like it shows below.
Then Close the Indexes and the new table and save it to make sure it accepts the Inventory Item data after indexed.

If it doesn't run a Totals query on the Item # field (With Item Number (Group By) and Item Number (Count) and see if there is more than 1 of any of the item numbers).

Then open the new table in design view and open the indexes again and add the rest of the indexes shown below:
Then save the Indexes and the Table.
Then rename the corrupted Inventory Table and rename the new table to Inventory
Test it out.

Follow similar logic and steps in Method 3 above but move the Inventory table DATA out and back into the database in this way:

Make a new Query and select all fields, Except the UniqueNumericID Field.
Save the Query.
Right click on the Query and Export out to Excel.
Then import the Excel file back into the Access Database. On the wizard steps where you get to define the Field type, make sure each field data type is the same type (including the Number type) as the DataDefs.mdb version of the Inventory table).
Complete the wizard and import the Data...
Open the data and verify proper record count.
Follow the steps in Method 3 above to add the Field Indexes in 2 phases.
Save, Rename, and Test...

Database corruption can come in many forms. Sometimes the 'data' is corrupted with unrecognizable characters. Sometimes the 'data' is ok, but the database object itself, and/or one or more of the table objects are corrupted.
If you can open the database...

1st try: Backup database, compact and repair. Check to see if the database is still acting corrupted.
If that doesn't work... (If you think you know which tables may have corruption, try recreating new, duplicate tables, moving the data from each suspects bad table into a new table with the exact same structure (and when you are completely finished, the exact same table name as well):

backup database, (Copy/paste)
compact and repair,
recreate any tables in questions (such as Invoice, Invoice LineItems, Sales Order, Sale Order Lineitems)

(Suggestion: Fix lineitem type types before the master table, such as Invoice Lineitems before Invoice)
copy Table in question (right click, click copy)
paste Table in question (right click, paste copy)

when prompted with 'Paste Table As', select 'Structure Only', then in the Table Name field type the Table Name (but with a slightly different name (since each table name in the db must have its own unique name).

when prompted with 'Paste Table As', select 'Append Data to Existing Table, then in the Table Name field type the name of the Table you just created (the structure for)

Reopen the original table in question and count the # of records. Do the same for the new records. (They should have the exact same # of records, or possibly a few less if any of the records in the original where corrupted).
Delete the older (possibly corrupted tables) and rename the new tables

Make sure the Name AutoCorrect features are turned off (Tools, Options, General for Access 2003)
Delete the original table suspected of corruption. (If warning appears about the table being is a relationship, click cancel, open the relationship windows and make a note of which table relationships the table is in. Then go back and delete the original table again, and when prompted, go ahead and delete the relationships and delete the table).
Rename the new table to the same name as the one you just deleted.

If necessary, recreate the relationships that you had to delete (to delete the older version of the table that you rebuilt).

close down the database, make a backup of the entire database, re-open the database, compact & repair, then try to duplicate the processes in OneSource that made you suspect 1 or more of the tables had corruption in them.

If that doesn't work, try making a brand new database, importing all objects from the old to the new

Usually this process will either reveal which table(s) have corruption and/or will simply correct the problem

NOTE: Be sure to open and check the 'indexes' on the main tables, such as Customers, Suppliers, Invoices, Invoice Lineitems, Sales Order, Sales Order Lineitems, Quotations, etc... Check for the existence of a 'Primary Key' on the field that should have a unique value. The index property should be set to Yes (No Duplicates).

copy and paste the database to make at least 1 extra copy of the database
Try using JetComp.exe to compact and repair (and retry to open)
Try creating a new database and importing a few of the tables into the new db. If possible, try importing all tables.
If you still cannot get the database to open up, you may either have to roll back to a backup of the file. (If this is not possible or feasible you may want to pay the $300 or so to have a specialize data repair company crack into the datafile and extract what they can extract. In some cases they can save all, in other cases some, and in a few cases little or none).