ACC: dBASE Unique Index Does Not Behave as Expected

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

If you choose a unique field when you link to a dBASE table, the index only contains 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 only limits 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 edit manually 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

   [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 field that is designated as unique allows you to enter duplicate information. However, when you query the file, the duplicate information does not show.

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

REFERENCES

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