ACC2: Unexpected Query Results with Attached xBASE Table

• Microsoft Access version 2.0

SYMPTOMS

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

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.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. Export the Order Details table using the Microsoft FoxPro version 2.6 format to a new file called ORD_DET.DBF.

3. Start FoxPro and open the ORD_DET.DBF file.

4. 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
   

5. Switch to Microsoft Access and create a new database called TEST.MDB.

6. 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.

7. Create the following new query and saved it as Query1:

         Query: Query1
         ------------------
         Type: Select Query
         Field: Order_Id
            Table: ORD_DET
         Field: Product_Id
            Table: ORD_DET
            Criteria: 24
   
   

8. Run Query1. Note that only 1 record is returned.

REFERENCES