Working with dBASE Databases

Borland’s dBASE product line includes the following versions supported by the Microsoft Jet Xbase IISAM driver: dBASE III, dBASE IV, and dBASE 5.0. dBASE 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 dBASE Data Types to Microsoft Jet Data Types

Microsoft Jet translates each dBASE 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 dBASE table through your application, the record is marked as deleted in the table. The record is not, however, removed from the table because dBASE allows users to recover deleted records until the table is packed. Because you cannot pack a dBASE table through Microsoft Jet, deleted records will continue to appear in your application’s data set.

To force Microsoft Jet to filter out deleted records in a dBASE table, you must set a setting 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 Microsoft Jet. This will hide deleted dBASE records from your application.

Handling dBASE Indexes

You can speed up Microsoft Jet access to dBASE tables by specifying the dBASE .ndx or .mdx index files to use. Index files are specified in an .inf file, which you can create either by linking your dBASE table through the Microsoft Access user interface or by creating a text file with the same file name as your dBASE database and appending an .inf extension. The following procedure describes how to create an .inf file for the hypothetical dBASE 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 dBASE III indexes, type the following:

   [dBASE III]

3. Assign an index number to the first index for the database. Number the first index NDX1, the second NDX2, and so on. If you want to specify multiple-index (.mdx) files for dBASE IV or dBASE 5.0 databases, use index numbers of the form MDX1, MDX2, 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. Don’t put any spaces in this entry. For example, to specify the index Custname.ndx as the first index and the unique index Custno.ndx as the second index, add the following entries:

   NDX1=CUSTNAME.NDX
   UNDX1=CUSTNO.NDX

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

   [dBASE III]
   NDX1=CUSTNAME.NDX
   UNDX1=CUSTNO.NDX

6. Save the .inf file to the directory that contains your dBASE 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, (for example, if the file is on a CD-ROM or a read-only network server), 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 dBASE database. If you associate index files with a dBASE database that is linked or opened from a Microsoft Jet database, you need to 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 dBASE databases are stored in the file system instead of in the database. If you want to link or open a dBASE database that has Memo fields, make sure the Memo field files are stored in the same directory as the database files.