DataWright Information Services

Consulting and Resources for Excel and Access




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:

error -1524: trying to copy a corrupt table

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:
the Lookup tab in a field created with the Lookup Wizard

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.
pick XML File from the dropdown menu
The default option is to export both the data and the
schema. Click OK.

export both the data and the schema

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.