[Return to contents]

Repairing corrupt index (.adx) files

Last updated: 27 Mar 2004 by XpertSS.com

(This widely affirmed procedure was submitted to the Approach Users Mailing list by Jerry Sikes, Unisource Converting, and has been update from later suggestions that he has made.)

  1: Get exclusive control of database files. This means that no-one other than yourself has the database open in any application.
  2: Create a directory named "Safe", or "Backup" or whatever is meaningful to you.
  3: Open each critical database, one at a time, as a new application with Approach. A default form and worksheet will be created.
  4: Run a find that gives you only 1 record in the found set. (Use Hide if necessary) This will create a minimal index and 1-record database when you do the next step.
  5: Export the "found set" to the safe directory, using the same database name. (Hint when Approach exports to this new directory, it creates a new one record index)
  6: Close the new application without saving. (Optional...I do not normally save these since Approach can recreate this at will)
  7: Repeat for all targeted tables.

The new safe copy indexes store the Long Field Name format that Approach uses, and the starting serial number for fields where that option is used.

If you need to "uncorrupt" an index:
  1: Get exclusive control of that database's files again.
  2: Copy the safe adx file back to the working directory.
  3: Reopen your application that uses the database and Approach should immediately launch into a "Smart Index Creation" mode. If not, do a "compress" of the database to force the reindexing.
  4: If you are using an old safe copy of the .adx file for a database with auto serial numbers, then you WILL need to manually reset the next serial numbers to the correct values.

It is important that you maintain a good revision control method. If you add fields to your working table, update the "safe copy" by exporting the 1 record again. Using an out-of-date index with an updated database structure could damage your data!

NOTE: Some people recommend that you compress your databases often as a corruption prevention method. XpertSS.com has not found this to be necessary or advisable unless you delete many records from your database and need to reclaim the space they are using in the files. It is more important to discover and fix the problems that caused the index corruption in the first place. Some things that are helpful are:

• Having backup UPS battery devices on your server and all workstations to prevent a power failure from interrupting data transfer over the network or writing incorrectly to your hard drive.
• Making sure that your validation rules prevent the "one" side of a join from accidentally becoming a "many" by validating join fields on the "one" side as both filled in and unique. If you use more than one field in a join, add a real field to that database with a modification formula that combines the join fields into one string and validates it as unique.
• Be very careful when importing records into databases. The Import Data process ignores your field validation rules and will let you import duplicate values into unique-validated fields. It also invalidates the database index which forces a reindex cycle that will impact user access to that database until the import has finished (locking messages, SmartIndexing messages, etc).

TIP: No amount of index backups will help if your database (.dbf and .dbt) becomes corrupted. Making regular backups of the entire set of files on a regular basis and storing them either in a fire-proof safe or off-site is highly recommended. And please, test your backup files to make sure they are OK and will restore when needed! Current technology makes backing up to CD/RW very simple and inexpensive, without resorting to compacted files on unreliable media like tape drives.