I thought my days of dealing with DBFs as a "production data" source were over, but HA (no such luck).
I recently had to retrieve, modify and replace some data that needed to be delivered in a DBF file.

Everything was fine until I realized / remembered the DBF driver does not ACTUALLY delete records from the data source -- it only marks them for deletion.  You are responsible for handling the "chaff" either by using a utility to remove deleted records or by simply ignoring them.  If imported into Excel, the marked-deleted records are ignored, but the file size will reflect the extra content.  After several rounds of testing CRUD, the output DBF was huge.

So, I went hunting for a method to "Pack" the records (removing deleted ones and resizing the DBF file) and eventually ran across the FOXPRO driver at ( http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx ).  Once installed, I changed the DSN in the code to the new one I created in the ODBC Administrator and ran some tests. 


Using MSQuery, I simply tested the raw SQL command Pack {tablename} and it WORKED!
One really neat thing is the PACK command is used like regular SQL instructions; "Pack {tablename}" is all that is needed.
It is necessary, however, to close all connections to the database (and re-open) before issuing the PACK command or you will get the "File is in use" error. 

Here is some C# code for a Pack method.

 
      /// <summary>
      /// Pack the DBF removing all deleted records
      /// </summary>
      /// <param name="strTableName">The table to pack</param>
      /// <param name="strError">output of any errors</param>
      /// <returns>bool (true if no errors)</returns>
      public static bool Pack(string strTableName, ref string strError)
      {
         bool blnRetVal = true;

         try
         {
            OdbcConnectionStringBuilder csbOdbc = new OdbcConnectionStringBuilder()
            {
               Dsn = "PSAP_FOX_DBF"
            };

            string strSQL = "pack " + strTableName;

            using (OdbcConnection connOdbc = new OdbcConnection(csbOdbc.ToString()))
            {
               connOdbc.Open();
               OdbcCommand cmdOdbc = new OdbcCommand(strSQL, connOdbc);
               cmdOdbc.ExecuteNonQuery();
               connOdbc.Close();
            }
         }
         catch (Exception exc)
         {
            blnRetVal = false;
            strError = exc.Message;
         }

         return blnRetVal;
      }

I thought my days of dealing with DBFs as a "production data" source were over, but HA (no such luck).
I recently had to retrieve, modify and replace some data that needed to be delivered in a DBF file.

Everything was fine until I realized / remembered the DBF driver does not ACTUALLY delete records from the data source -- it only marks them for deletion.  You are responsible for handling the "chaff" either by using a utility to remove deleted records or by simply ignoring them.  If imported into Excel, the marked-deleted records are ignored, but the file size will reflect the extra content.

So, I went hunting for a method to "Pack" the records (removing deleted ones and resizing the DBF file) and eventually ran across the FOXPRO driver at ( http://msdn.microsoft.com/en-us/vfoxpro/bb190233.aspx ).  Once installed, I changed the DSN in the code to the new one I created in the ODBC Administrator and ran some tests. 


Using MSQuery, I simply tested the raw SQL command Pack {tablename} and it WORKED!
One really neat thing is the PACK command is used like regular SQL instructions; "Pack {tablename}" is all that is needed.
It is necessary, however, to close all connections to the database before issuing the PACK command. 

Here is some C# code for a Pack method.

 
      /// <summary>
      /// Pack the DBF removing all deleted records
      /// </summary>
      /// <param name="strTableName">The table to pack</param>
      /// <param name="strError">output of any errors</param>
      /// <returns>bool (true if no errors)</returns>
      public static bool Pack(string strTableName, ref string strError)
      {
         bool blnRetVal = true;

         try
         {
            OdbcConnectionStringBuilder csbOdbc = new OdbcConnectionStringBuilder()
            {
               Dsn = "PSAP_FOX_DBF"
            };

            string strSQL = "pack " + strTableName;

            using (OdbcConnection connOdbc = new OdbcConnection(csbOdbc.ToString()))
            {
               connOdbc.Open();
               OdbcCommand cmdOdbc = new OdbcCommand(strSQL, connOdbc);
               cmdOdbc.ExecuteNonQuery();
               connOdbc.Close();
            }
         }
         catch (Exception exc)
         {
            blnRetVal = false;
            strError = exc.Message;
         }

         return blnRetVal;
      }