Microsoft KB Archive/132012

= ACC2: Unexpected Query Results with Attached xBASE Table =

Article ID: 132012

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132012



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



SYMPTOMS
When you run a select query based on an attached Microsoft FoxPro or dBASE table with the criteria applied to an indexed field designated as the unique record identifier, the Microsoft Access version 2.0 Service Pack produces unexpected query results. For example, the query returns only 1 record instead of all the records that match the criteria applied to the unique record identifier field.



CAUSE
If the unique record identifier field contains duplicate values, the Microsoft Jet database engine version 2.5 returns the first record that matches the criteria in the query.

NOTE: This behavior differs from the Jet database engine version 2.0 that was shipped with Microsoft Access version 2.0. The Jet database engine version 2.0 returns all the records that match the specified criteria in the unique record identifier field, not just the first matched record.



RESOLUTION
To work around this behavior, re-attach the table without a unique record identifier, and then run the query again. To do so, follow these steps:


 * 1) In your database, delete the attachment to the FoxPro or the dBASE table.
 * 2) Re-attach the table and specify an index file. In the Select Unique Record Identifier dialog box, click Cancel to avoid specifying a unique identifier.
 * 3) Run the select query again. Note that you receive all the records you expect instead of only 1 record.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Open the sample database NWIND.MDB. Export the Order Details table using the Microsoft FoxPro version 2.6 format to a new file called ORD_DET.DBF. Start FoxPro and open the ORD_DET.DBF file.

  In FoxPro, to create an index file called ORD_DET.CDX., create an index on the Product_Id field by typing the following lines in the Command window:

     USE ORD_DET INDEX ON PRODUCT_ID TAG PRODUCT_ID USE QUIT </li> Switch to Microsoft Access and create a new database called TEST.MDB.</li> In TEST.MDB, attach to ORD_DET.DBF as a FoxPro 2.6 table. Be sure to specify ORD_DET.CDX as the index and select Product_Id in the Unique Record Identifier dialog box.</li>  Create the following new query and saved it as Query1:

<pre class="fixed_text">     Query: Query1 --     Type: Select Query Field: Order_Id Table: ORD_DET Field: Product_Id Table: ORD_DET Criteria: 24 </li> Run Query1. Note that only 1 record is returned.</li></ol>

<div class="references_section">