Rebuilding a Corrupt Access Table

Recently, a client's database started behaving strangely. It had worked well for about 10 years but now there were signs that all was not well;

• Queries, forms and reports started giving error messages about missing fields. In some cases the 'missing' field had a name and we could verify that it existed; in others the message was more cryptic, along the lines of 'The field called | could not be found'.
• Queries could be edited in SQL view but not Design view.
• Code would occasionally refuse to run; other times it was fine.
• To find the culprit I asked the client to import all of the tables into a fresh database. One table could not be copied; it was the main data table. Time for a recovery operation.

When you try to copy a corrupt table, this is the error message:

This article details the steps that I took to rebuild the table, in the hope that you will find it useful.

Make a copy of the database

Kick everyone out of Access, navigate to the database in Windows Explorer, and make a copy. Do the recovery operation on the copy. That way, if users need to use the database for searching you can minimise the inconvenience. And you have a fall-back in case of disaster.

Warn the users that any data entry they do during the repair process may need to be repeated. For this reason, repairing a table is best done when no-one needs to use the system.

Recover the data

If possible, use a Make-Table query to push the data into a new table. However, if it can't be copied the Make-Table query won't work and you may need to copy and paste to another application. The table had 28 fields and around 40,000 rows so it was not too big for Excel.

Check the data display before copying it

A few of the fields were number fields, displaying text. This betrayed their history as Lookup fields. Because we wanted to keep the numbers the field properties needed to be changed.

• Select the field in Design view
• In the Properties area, click the Lookup tab. You will see something like the image below. Change the width of the first column (circled in red) to something greater than 0; I usually use 3.
• Repeat for any other lookup fields, and save the table.
• Close the table.

The lookup tab looks like this:

Option 1: Copy the data to Excel

• Launch Excel.
• Restore the Access window so you can see Excel, then select the icon for the table and drag and drop it into cell A1 of the Excel worksheet.
• After a short wait (maybe a minute or so, depending on the amount of data) you will have the table's data in Excel.

Option 2: Export the data to an XML file

If you are using Access 2003 or higher, consider using XML as your data store.

• The export/import steps are wizard driven.
• The data is structured, so each field has the correct data type.
• XML files can be read by virtually any database application, so it's a future-proof archive.

In Access 2002 or 2003, do this:

• Select the table name in the database container.
• File > Export, and select XML as the data type.
• Click OK, and in the next dialog select the default option to export the data and the schema.

Using Access 2007, these are the steps:

• Select the table name in the navigation pane.
• In the External Data tab, Export group, select More and pick XML Export.
• Browse to the directory where you want to export the data; then choose in the next dialog to export both the data and the schema.
• All going well, your data will be exported.

Pick XML File from the dropdown menu.
The default option is to export both the data and the schema. Click OK.

Option 3: Export to a delimited text file

If the table is too big for a spreadsheet you can use code to transfer it to a delimited text file. This is quick but .CSV files do strange things with dates, and you may need to clean them up. See this article for one approach.

Build a new version of the table

Create a new table in Design view. Reproduce the design exactly; field names, data types, field sizes, default values, indices. Make an exception for lookup fields. Keep them as straight Number fields, and don't rebuild the lookup properties. Combo boxes on forms do the same job, and lookup fields are best avoided.

Name the table; give it the same name as the original but with the suffix _2 (eg, the copy of Bookings is Bookings_2).

Optional: If you decide to use the ADO code method for pushing records form Excel to Access (see below), copy this new table and give it the suffix _3.

Note: If you used the XML Export, building the new structure is unnecessary; the table is created for you during the import step. All you have to do is to set the appropriate field properties.

Load the data into the new table

Option 1: Use drag and drop to transfer the data to Access

This is the quickest way to get the data into Access. Do the following:

• In Excel, select the whole table (Select A1, then Ctrl+Shift+8)
• Click on the edge of the selection, hold down the Ctrl key, and drag and drop onto the Tables section of Access.
• Accept the prompt about column headings. The new table will be created with the worksheet name.

Note: There are disadvantages to this approach. Access tries to determine the data type as you import, and uses the first few rows to do this. If those rows are blank for any fields Access will regard them as Text fields, and may refuse to import data containing numbers or dates. You can get around this by selecting the columns and defining them as Number, Currency, Date or Text using the Format menu. Save the file, then do the import.

Option 2: Use ADO to populate the new table from the Excel file

Although this takes a bit more work than drag and drop, you get to define what gets imported, and the data will go into a table with the correct data types (because you built it). There is a detailed article here on how to push data to Access with ADO. You will need to change the name of the target database and table; and the table should not have any spaces in the name.

Also, if the data has blanks you will want to skip those in the import process. To do that I modified the sample code, as shown below.

Original code:

    For i = 2 To Rw
       rst.AddNew
       'in this case we are loading 7 fields. Adjust to suit.
       'field headings are in row 1 of the worksheet.
       For j = 1 To 7
          rst(Cells(1, j).Value) = Cells(i, j).Value
       Next j
       rst.Update
    Next i

Is replaced by this:

    For i = 2 To Rw
       rst.AddNew
       'in this case we are loading 7 fields. Adjust to suit.
       'field headings are in row 1 of the worksheet.
       For j = 1 To 7
          If Not IsEmpty(Cells(i, j)) Then
             rst(Cells(1, j).Value) = Cells(i, j).Value
          End If
      Next j
       rst.Update
    Next i

Option 3: Import the text file

If you had to use a text file, you can import it into Access.

• In 2003 and earlier, choose File > Get External Data.
• In 2007, External Data > Import > Text File.
• From there the steps are the same. Browse to the file, click OK, and go through the Wizard.
• Import into a new table

Option 4: Import an XML file

If your data is in XML format, the job couldn't be simpler.

In Access 2002 and 2003, do this:

• File > Get External Data > Import
• In the dialog, select XML files as the file type and browse to the directory where you saved the XML export.
• Pick the .xml file, click OK.
• In the next dialog select the table name and click OK.

In Access 2007 do this:

• External Data > Import > XML File
• Browse to the file, OK.
• Select the table name from the next dialog, OK.

In either case, the data is imported into a new table with the correct data types. All you have to do is update the field properties to match the original table.

Populate the new table

Now that the data is in Access, you may need to transfer it into the new table that you built earlier. For the XML and ADO methods this is unnecessary. For the others, build an Append query to push all of the data from your imported table to the replacement table that you built earlier.

If you have an AutoNumber primary key field, you can push the existing values from your import table into that field with an Append query. It's a life saver.

Run some data checks

Now to check that all the data survived the round trip. Build a Totals query on the original table. Use about 4 fields; get the Count of the records and the Sum of three different numeric fields.

Now compare those totals with a similar query on the imported data; they should match. If not, some records may have failed the import process; you can tell that by the record count. Any missing records can be identified by running a Find Unmatched query on the two tables. Once you identify the reason that they didn't import (usually incompatible data type or null values in a field that won't accept nulls), fix the problem and import those records again.

Once the totals match the originals, we're almost there.

Assuming that you have been working in a copy of the database, go to the working version and import the new table.

Break the relationships to the original table

Before we can rename the old table, any relationships involving that table need to be broken. Go into the Relationships view (On the main toolbar in Access 2003 and earlier; in the Database Tools tab for Access 2007).

Double-click any links leading to the corrupt table. Write down the tables, field names, and whether or not referential integrity is enforced etc. Close and save when finished.

Rename the old table and the replacement

Now, rename the corrupt table (give it a suffix like _old or _broken) and also rename the new table, giving it the same name that the corrupt table originally had.

Rebuild the relationships

Back to the relationships window; rebuild all of the relationships that you broke previously. Close and save.

Test

Check out the database with the fixed table. The error messages and the inconsistent behaviour should be gone; and it should also be faster.

Wrapping up

Repairing a corrupt table is not a small job but with a couple of hours' work it can be done successfully. Make sure to keep regular backups, compact and repair the database regularly, and hopefully this won't happen to you often.

  Home | About | Links | Contact | Excel | Access