ACC2: Unexpected Query Results with Attached xBASE Table
ID: Q132012
|
The information in this article applies to:
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:
- In your database, delete the attachment to the FoxPro or the dBASE
table.
- 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.
- 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
- 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
- Switch to Microsoft Access and create a new database called TEST.MDB.
- 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.
- 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
- Run Query1. Note that only 1 record is returned.
REFERENCES
For more information about attaching tables, search for "Attach Table," and
then "Attach Table Command (File Menu)" using the Microsoft Access Help
menu.
Keywords : kb3rdparty IsmXbase
Version : 2.0
Platform : WINDOWS
Issue type : kbprb