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;
|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.
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.
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.
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.
|The lookup tab looks like this:|
If you are using Access 2003 or higher, consider using XML as your data store.
In Access 2002 or 2003, do this:
Using Access 2007, these are the steps:
|Pick XML File from the dropdown menu.|
|The default option is to export both the data and the schema. Click OK.|
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.
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.
This is the quickest way to get the data into Access. Do the following:
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.
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.
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
If you had to use a text file, you can import it into Access.
If your data is in XML format, the job couldn't be simpler.
In Access 2002 and 2003, do this:
In Access 2007 do this:
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.
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.
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.
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.
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.
Back to the relationships window; rebuild all of the relationships that you broke previously. Close and save.
Check out the database with the fixed table. The error messages and the inconsistent behaviour should be gone; and it should also be faster.
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.