ACC2000: dBASE Unique Index Does Not Behave as Expected (199136)

SYMPTOMS

If you choose a unique field when you link a dBASE table, the index contains only records that are unique with respect to the specified index expression. For example, a .dbf file may contain "n" records, while the unique index may contain "m" entries, where "n" does not equal "m." This means that when the database is viewed by a UNIQUE index, it may appear to have fewer records then it actually does.

CAUSE

There is no concept of primary indexes in dBASE prior to version 7. Up to version 6, all indexes are taken as secondary indexes. The dBASE command SET UNIQUE ON limits only which records are displayed. Access, however, presently does not support dBASE later than version 6.

RESOLUTION

You can resolve this issue by one of the following three methods:

One solution to prevent this scenario is to SET UNIQUE OFF and REINDEX in dBASE. This allows all records to be displayed.
Another solution is to not choose a unique field when you link the dBASE table. However, this option is not available unless you have installed the Borland Database Engine (BDE). For more information about the BDE, please visit the following Web address:
http://info.borland.com/devsupport/bde/
NOTE: Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.
A third solution is to manually edit the .inf file created when you link a dBASE table. The following are examples of the .inf file.

Contains Unique Field:
```
   [dBASE III]
   NDX1=C:\ACCESS\SAMPLES\city.ndx
   [UIDX1 city#idx]
   NDX1=C:\ACCESS\SAMPLES\city.ndx
```
Without Unique Field:
```
   [dBASE III]
   NDX1=C:\ACCESS\SAMPLES\city.ndx
```

MORE INFORMATION

The following example illustrates the concept.

A dBASE file contains the following records:

   Company Name        Address        Phone
   ------------------------------------------------
   ABC                 123 4th ST.    (888)888-8888
   ABC                 123 4th St.    (999)999-9999
   CDF                 1st S.         (777)777-7777

The index is created on the Company Name field. If the Unique setting is on in dBASE, only 2 records are displayed (the 1st and 3rd).

The field that is designated as unique allows you to enter duplicate information. However, when you query the file, the duplicate information does not show.

The following table outlines what versions of dBASE Access 2000 can link to:

Version	Behavior
dBase 3, 4, 5 ISAM Only	Import\Export\Link-Read Only
dBase 3, 4, 5 ISAM w/BDE	Import\Export\Link-Read Write
dBase 7-8	No support provided

REFERENCES

For more information about linking to dBASE files, click Microsoft Access Help on the Help menu, type link dbase files in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For additional information about linking to dBASE files, please click the article number below to view the article in the Microsoft Knowledge Base:

230125 ACC2000: Using dBASE Data with Access 2000 and Jet

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.