I am using VB to write an application that queries and updates .DBF files.

There's a 4-byte header (offsets 4-7) in a .DBF indicating how many records are in the file. See here: http://www.dbf2002.com/dbf-file-format.html

When I do an SQL "INSERT INTO" statement, this header is updated correctly.

When I do "DELETE FROM", the header isn't even touched.

Example: I insert 1000 records into a table. The header shows 1000 records. When I remove all 1000 records, the header still shows 1000. A "SELECT COUNT(*)" statement returns a value of 0.

If I open the .DBF in Excel (I don't have Visual FoxPro) and simply save it, the header is updated correctly.

Anyone familiar with this? Here's a snippet of my code when I'm doing a delete:

Dim Conn = CreateObject("ADODB.Connection")
Conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & datapath & ";Extended Properties=DBASE IV;")
Conn.execute("DELETE FROM table WHERE somerecord = somevalue")
Conn.close()

TIA

After you have used the SQL code to delete the records, is the actual DBF file size smaller?  I suspect not.

The way a DBF table is designed and structured is that it can contain both active and deleted records.  When you delete a record it is just flagged as deleted and is still there.  This makes it possible to RECALL a deleted record and not lose it's data.  It is only when the table is PACKed that the actual records marked as deleted that the records are removed , the file is smaller and the record count or RECCOUNT() is reduced.

The flag for the deleted record is an asterisk placed in the first true field of the record segment in the table.  For example, say you have a table with 2 fields and each is 10 characters long.  When you add a record, the table will actually be 21 bytes longer (1+(2*10)) ... 1 byte for the deletion flag and 20 bytes for the to fields.  Normally the delete flag is blank (a space, character 32) but when deleted the record is updated with an asterisk (*) in that flag.

TIA,

Mark is correct.  If you haven't "PACK"ed the DBF after the delete, the counts wouldn't change.   The records noted should be marked for deletion and need to be packed out to remove them completely.

Jim C.  
 

Not sure how to pack my tables, though. I've searched high and low and I'm coming up with nothing. Only way I know how to do it is open/save in Excel. Thanks again.