How can I minimize index problems on my tables?

[carlosest]

I wrote accounting program using VFP ver. 6.0 and since January 2003 I have sold over 300 copies. I use Database with 35 tables all of them indexed. I have been plagad with taables loosing index roughly three times a day out of this number of copies on the market which makes it about one percent of copies out, every day. At this rate by the end of the year when I expect to have 1000 copies out I will be overhelmed. I wrote program to reindex all tables but it works only about 40% of the time. When reindexing program doesn´t work I ask customer to bring CPU to my shop but this is not the right answer, I think. Any suggestions as to how to treat this problem and minimize it?

Thanks in advance

carlosest

 

[Mike Gagnon]

carlosest

Any ideas as to why the indexes get corrupted? Power failure? Or wrong use of the program? Somoeone using the data through " the back door"? If you could pin-point the cause, that might help.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[fluteplr]

Also stay away from any index with a function like any of the trims that can cause the index values to be of different lengths. I have found these to be inherently more unstable than 'fixed length' indexes. If you must trim to do a concatenation or something similar pad the result back out to a fixed length.

 

[Mike Lewis]

Carlosest,

Your first job must be to find out why the indexes are getting corrupted. Is it any particular index? Does it happen when a particular part of the application is running?

If you can't find out why the corruption occurs, how are you re-creating the indexes? Doing a REINDEX is not always a good idea. If the CDX header is itself corrupted, either REINDEX won't run or it won't cure the corruption.

A better way is to use DELETE TAG to delete the index, then run the INDEX command again. But you will have to hard-code the routine for each of your indexes, which is tedious. Also, deleting the tags will destroy any persistent relationships in your DBCs, although that might not matter at the user's site.

Final thought: Consider getting Stonefield Database Toolkit (

http://www.stonefield.com).

I have used this successfully in a similar situation. But it is no substitute for preventing the corruption in the first place.

Mike

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk