FoxPro Tip: Be Aware of Disappearing Index files (IDX)

There's still a lot of older FoxPro code that relies on the old IDX file - especially for temporary indexes that only need to be created on an as needed basis.

Now, you can create indexes on temporary cursors just as with regular tables but there's a trick to be aware of.

When you create an IDX file on a DBF file that physically exists , the IDX file will still be there after you close the table.

However, if you create an IDX file on a temporary cursor, as soon as you do anything that may close the index, the IDX file is instantly deleted.

Try it:
USE HOME(2)+"DATA\CUSTOMER"
INDEX on customer.city TO t
DIR t.idx && the file is there
SET INDEX TO
DIR t.idx && the file is STILL there
SELECT * FROM customer WHERE region ='WA' INTO CURSOR wacust
BROWSE
INDEX on city TO wat
DIR wat.idx && the file is there
SET INDEX TO wat.idx && Error file does not exist

Why ? Because the SET INDEX TO statement clears the previous index and attempts to reset it. But when it clears it, FoxPro recognizes that it is a temporary cursor and thus deletes the index.

The solution?

issue a SET INDEX TO ADDITIVE and that should keep it there.