Microsoft KB Archive/163279

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

Article ID: 163279

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q163279



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 given 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. All indexes are taken as secondary indexes. The dBASE command SET UNIQUE ON limits only which records are displayed.



RESOLUTION
One way to prevent this scenario is to SET UNIQUE OFF and REINDEX in dBASE. This will allow all records to be displayed. Another solution is not to choose a unique field when you link the dBASE table. A third solution is to manually edit the INF file that is created when you link a dBASE table. 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 helps to illustrate 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, then only 2 records would be 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.

