Compact dbf files

[tiborgh]

I have a process that has to write data into a dBase IV file. To access the dbf file I use the following connection string:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data;" & _
"Extended Properties=dBASE IV;"

I've noticed that after inserting and deleting several times a big number of records, the size of the dbf file continued to increase. As far as I know, records in dbf files are not deleted phisycally, just marked as deleted. Does anybody know how to compact programatically the dbf files?

[stp]

Here is the 'Least Elegant' method: (I've done this myself)

You will need an empty copy of the dbf file stashed away somewhere.

1. Copy the empty file into the desired folder.
2. Establish a connection to this new file.
3. Run a multiple record insert query:
INSERT INTO newtable (field1, field2[, ...])
SELECT field1, field2[, ...]
FROM oldtable
4. Remove the old dbf file and rename the new file.

You will probably need dBase to create your empty file. If the dbf file has index files associated with it will complicate matters.

I hope this helps (and that I am not just leading you where you didn't want to go originally).

[tiborgh]

Thanks,

I will consider your solution, since for my workaround (a DOS utility that packs dbf files and is executed at the end of my process) is "on hold" until I will find that utility...

tibi