Inserting records and sorting without indexes

[coco86]

I've got a problem I hope someone can help with.

I'm writing an application in Visual Basic to manipulate a dbase III table. I'm trying to copy several records and insert them back into the table. The table has no indexes and for circumstances beyond my control, I can't add any either.

There is a field in the table ("SORT&quot that determines the order the records should be in. This column contains numeric data but it is a text field (once again, I can't change it) so if I try to actually sort on the field in my
SQL statement, everything gets all jumbled (So I can't do it in a single step using a union query)

I've actually figured out one way that works. Step one is to insert the records using a single SQL statement:

MyDb.Execute "insert into OLDTABLE select * from OLDTABLE 
        where SORT in ('" & LIST_OF_RANDOM_NUMBERS& "')"

The second step is to create a new table by copying all the records in the correct order like this:

MyDb.Execute ("SELECT * into NEWTABLE FROM OLDTABLE 
     ORDER BY Val([SORT])")

This works. Unfortunately, though, the bigger the table, the longer it takes. A table with a quarter million records took over twenty minutes and there could potentially be even more records.

Does anyone have any ideas on how I could accomplish this task in a shorter period of time?


-Coco

 

[mkrausnick]

dBase tables are fixed length, uncompressed files. In dBase, Clipper, Foxpro, and other "xBase" derivatives, you can access the "record number" of the record through a a function called RECNO(). The language calculates the starting offset of the record within the file based on the "record number" and record length, plus making allowance for the table header.

Maybe you can utilize this information somehow to calculate the physical position of the record within the table and utilize low-level file I/O rather than SQL to update the table. I used to have the header layouts for all those xBase formats - I'll look for them and re-post if I find them.

Mike Krausnick
Dublin, California