Working with FoxPro Databases

The Microsoft FoxPro product line includes the following versions supported by the Microsoft Jet Xbase IISAM driver: FoxPro 2.0, FoxPro 2.5, FoxPro 2.6, FoxPro 3.0, and FoxPro DBC. FoxPro databases differ from Microsoft Jet databases in four major areas: data types, handling of deleted records, specification of indexes, and storage of Long Text (Memo) fields. The following sections describe these differences.

Converting FoxPro Data Types to Microsoft Jet Data Types

Microsoft Jet translates each FoxPro data type into the corresponding Microsoft Jet data type when your application reads the data. The following table shows the one-to-one correspondence between data types.

Handling Deleted Records

When you delete a record from a FoxPro table through your application, the record is marked as deleted in the table. The record is not, however, removed from the table because FoxPro allows users to recover deleted records until the table is packed. Because you cannot pack a FoxPro table through the Microsoft Jet database engine, deleted records will continue to appear in your application’s data set.

To force Microsoft Jet to filter out deleted records in a FoxPro table, you must set a value in the registry. You can do this by setting the Deleted setting under the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Xbase key to 01 (True) and restarting the Microsoft Jet database engine to hide deleted records from your application.

Handling FoxPro Indexes

You can speed up Microsoft Jet access to FoxPro tables by specifying which FoxPro .idx or .cdx index files to use. Index files are specified in an .inf file, which you can create by linking your FoxPro table through the Microsoft Access user interface or by creating a text file with the same file name as your FoxPro database and appending an .inf extension. If there is no .inf file but there is a .cdx index file in the same directory as the database file, the IISAM driver will automatically use that index file. The following procedure describes how to create an .inf file for the hypothetical FoxPro database Sales.dbf.

1. Create the text file Sales.inf.
   
   
2. On the first line, add the database type identifier enclosed in square brackets. For example, to specify FoxPro 2.0 indexes, type the following:

   [FoxPro 2.0]

3. Assign an index number to the first index for the database. Number the first index IDX1, the second IDX2, and so on. If you want to specify combined-index (.cdx) files for FoxPro databases, use index numbers of the form CDX1, CDX2, and so on. You can specify unique indexes by prepending a “U” to the index number.
   
   
4. Follow the index number with an equal sign and the file name of the index you want to specify, including the extension. Do not put any spaces in this entry. For example, to specify the index Custname.idx as the first index and the unique index Custno.idx as the second index, add the following entries:

   IDX1=CUSTNAME.IDX
   UIDX1=CUSTNO.IDX

5. Repeat steps 3 and 4 until you have specified all indexes for the database table. Your completed .inf file should look similar to the following:

   [FoxPro 2.0]
   IDX1=CUSTNAME.IDX
   UIDX1=CUSTNO.IDX

6. Save the .inf file to the directory that contains your FoxPro database. Alternatively, you can create an INFPath setting in the registry to point to the path where the .inf file is stored. This is useful if you cannot store the .inf file in the same directory as the database or if you want to keep all .inf files in one shared location for easy maintenance.

   To create an INFPath setting in your registry, you must manually add the registry setting in the Registry Editor as a string value. To add the setting, open the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Xbase key in the registry. On the Edit menu, point to New, and then click String Value. Type INFPath as the name and type the full directory or network path to the .inf file in the data field. Save the registry changes and restart Microsoft Jet to use this setting.

Keep in mind that Microsoft Jet will need to periodically update your index files as you change data from within your FoxPro database. If you associate index files with a FoxPro database that is linked or opened from a Microsoft Jet database, you must make sure the indexes are available to your Microsoft Jet database as well.

Important Don’t delete or move the index files or the .inf file without updating associated references (such as the registry). If Microsoft Jet doesn’t have access to up-to-date index information, it cannot correctly process queries.

Handling Memo Fields

Memo fields for FoxPro databases are stored in the file system rather than in the database. If you want to link or open a FoxPro database that has Memo fields, make sure the Memo field files are stored in the same directory as the database files.