Microsoft KB Archive/302968

= ACC2000: Query of ODBC Linked Data Returns Unexpected Results =

Article ID: 302968

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q302968



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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
When you attempt to query a table or view that is linked from an open database connectivity (ODBC) data source in Access, the query may return records that do not meet the criteria that you specify.



CAUSE
This behavior can occur if at least one of the following conditions is true:  The linked table or view does not contain a unique index. The unique index contains duplicate data. The unique index is a compound primary key that contains one or more fields of the following data types:

- Char (or fixed-width text field)

- Num (or floating point decimal)





RESOLUTION
To resolve this behavior, delete and then recreate the existing view or table link.
 * Make sure that you specify a unique record identifier that has a data type that Access can properly map to, such as a Timestamp, Long Integer, or Integer data type.
 * Make sure that the specified unique record identifier contains only unique data.



Steps to Reproduce the Behavior

 * 1) Create a new SQL Server table that has the following three Char fields: UniqueIdentifier, FirstName, and LastName. Do not specify a primary key for the table.
 * 2) Add ten records to the new table, and give four of them the same UniqueIdentifier value.
 * 3) Create a new Access database, and then link the database to your new SQL Server table. When a prompt asks you for a unique record identifier, specify the UniqueIdentifier field.
 * 4) Create a new query that is based on the linked table, and include all of the table's fields.
 * 5) Run the query. Note that the records that are returned do not match the records that are stored in the SQL Server table.

Additional query words: prb

Keywords: kbprb KB302968

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.