Choosing the Repair Tables option does one additional step before going on to do a rebuild indexes. It trawls through the tables and validates all the records as much as possible. If it finds errors where possible the record is repaired and if not the record is deleted. Any records that have previously been marked for deletion are also deleted at this stage. This results in a smaller data file than before.
The resulting table is written to a new file whilst it is being compiled. When it is complete the names are swapped so that the new table has the original file name and the replaced table is given a .sav file extension. There are now effectively two copies of the table. Once you have tested the repaired database and are happy, you can delete all the .sav files.
After the new table is in place the system goes on to perform a normal rebuild of the indexes.
When should you repair a table?
There are three reasons why you might want to repair a table.
You are about to perform a version update. Whilst this is not normally necessary, it may become so if the update has previously been attempted and failed for whatever reason. As the version update may have cause to change the database structure it may fail if it encounters corrupt data. The repair process can eliminate this data. If this were the reason you could only rebuild the table that caused the failure, in most cases though you would repair all tables.
You have deleted a lot of records and wish to compress the database. This is not really necessary. However should you need to permanently delete a record so that it cannot be recovered, or you are genuinely very low on disk space then you may wish to do this.
You still have a problem that the rebuild indexes process has not fixed. On some occasions the rebuild index process may not be able to cope with a corruption of the data file and may keep producing the same wrong result. In this case a repair may be the answer to delete or correct the corruption so the rebuild process works.
Why should you repair?
You should repair a table because:
Otherwise updates may not work correctly
Data tables may be larger than necessary or deleted records may be recoverable.
The rebuild index process may not work correctly.
How do you repair?
The steps are exactly as for doing a ‘Rebuild Index’ but selecting the 'Repair Table' option instead.