ACC2000: dBASE Unique Index Does Not Behave as Expected

ID: Q199136

The information in this article applies to:

Microsoft Access 2000

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 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 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://www.borland.com/bde
NOTE: The third-party contact information included in this article is provided to help you find the technical support you need. This contact information is subject to change without notice. Microsoft in no way guarantees 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:

Q230125 ACC2000: Using dBASE Data with Access 2000 and Jet

The third-party products discussed here are manufactured by vendors independent of Microsoft; we make no warranty, implied or otherwise, regarding these products' performance or reliability.

Additional query words: Fox attach prb

Keywords : kb3rdparty kbdta IsmXbase
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb