Hi Henry,

Adding new fields to tables as an application evoles and then updating the tables already distributed to customers is always a problem. I'm sure there are many way this can be done.

My solution was do have a base set of tables with [mst] in the 6th, 7th, and 8th positions of the table name. The mst is an abreviation designating a master tables. I can visually add fields to the master tables like in any database. When a customer downloads the application and must update their tables to add new fields to accomodate the new code, they run a procedure called validate datebase. I strongly encourage the customers to make a backup of their database before running this procedure which is accessed via a menu option on a top level form.

Basically the procedures removes tables from the DBC. Then the database container is deleted. A copy structure of the [mst] tables is performed to create temporary tables. The original records are appended into the respective temporary tables. If the temporary tables build successfully, the original tables are deleted and the temporary tables are given the name of the delted original tables. The updated tables are then added to a new DBC. A default referential integrity procedure in the form of a text file is then appended into the DBC. Tables are packed and indexes deleted and rebuilt.

The procedure can take from between a few minutes to thirty minutes depending whether the major 5 transaction tables average 15,000 or 250,000 records. I havn't done testing beyond the 250,000 record range which I used to optimize code. If all goes well, at the end of the procedure the client will have tables that are identical to the master table complete with any changes to fields either delete or added.


Leland