Reconstructing SEURDATA.MDB

making it better, stronger, faster ... sorry, wrong TV show ...

In April 1998, the YNHH2 server crashed multiple times. We believe this resulted in corruption of the SEURDATA.MDB file, which led to corrupted information in the Teleform-parsed flat files and an inability to process them into EAV format for storage.

The SEURDATA.MDB file was compacted about a week ago. The first attempt to compact it revealed errors in need of repair. The database was repaired using Access's built-in function (selected from the File menu when there is no database open). However, this was not successful, so ultimately, the database had to be reconstructed, using the following procedure:

  1. Bring a copy of a blank version of the SEURDATA.MDB database. (This will be called SEURDATA.BAK, and will be kept on the backup floppy disk and on the web server).
  2. Copy it onto the YNHH2 directory under a separate name, say, as W:.MDB. Open this database in Access.
  3. Tables which establish a primary key by sequentially assigning numbers will have to have their data types changed so that they are not sequentially assigned. (These are the Encounter table, the MeasEvt table, the EncEvt table, and the VaccEvt table.) This is important to assure that primary key information is not reassigned when data is copied from one database to another.
  4. Open the SEURDATA.MDB file in another instance of Access. Copy and paste the data from each table, table by table, into their corresponding tables in the backup database.
  5. Re-establish the relationships between the tables of the SEURDATA.MDB database. (These are detailed in the documentation elsewhere.)
  6. Finally, close both instances of Access. Rename SEURDATA.MDB (perhaps SEURDOLD.MDB) and then rename the reconstructed database as SEURDATA.MDB
  7. Pray it all works!

We suspect that the database will need to be compacted and/or repaired on a fairly regular basis, perhaps quarterly, because some performance enhancements were noted when the database was compacted.

Created by James C.S. Liu, M.D. on 28 August 1998
Updated by Geoffrey J. Corb on 14 September 1998