Microsoft KB Archive/199136

= ACC2000: dBASE Unique Index Does Not Behave as Expected =

Article ID: 199136

Article Last Modified on 2/24/2003

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q199136



This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.



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: <pre class="fixed_text">  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:

<div class="references_section">