There is a flaw in VFP tables/indexes when you are adding records at a high transaction rate (1 record per 2 -3 seconds) from multiple sources simutaneously (and even from a single source) which will cause corruption to the primary key index and sometimes result in the header corruption.


From January 2001 to March 2001, I worked with Microsoft on trying to figure out the problem why the primary key (integer) field was always getting corrupted and once in awhile the header. We looked at our network harware, looked at WAN connections, we looked at server hardware, we look at the NT operating system and, we looked at application coding.

We found no permanent fix. We found a work around where I created two dummy character and integer fields physically before the primary key field in the table which would capture the corruption. This almost has eliminated the problem and brought our data integrity to about 99 %.

We do weekly maintenance on the various tables to rebuild the primary keys on the high volume tables. The typical high volume table) with reach the 2GB limit in 5 months. We dump data out when they reach 1 GB since performance greatly is impacted.

People will suggest for me to move everything to SQL, or Oracle, etc. I do use SQL Server for other parts of the application, but this is for another thread and I can explain the various reasoning there when the time comes.