KB163279 - ACC: dBASE Unique Index Does Not Behave as Expected

Article ID: 163279

Article Last Modified on 1/19/2007

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.

REFERENCES

For more information about linking XBase files, search the Help Index for for "dBASE."

dBASE is manufactured by Borland, a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

Additional query words: Fox attach

Keywords: kb3rdparty kbprb KB163279