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:
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:
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:
230125 ACC2000: Using dBASE Data with Access 2000 and Jet
The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.
Additional query words: Fox attach prb
Keywords: kbprb kb3rdparty KB199136
