ACC2000: Query of ODBC Linked Data Returns Unexpected Results (302968)

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.

MORE INFORMATION

Steps to Reproduce the Behavior

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.
Add ten records to the new table, and give four of them the same UniqueIdentifier value.
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.
Create a new query that is based on the linked table, and include all of the table's fields.
Run the query. Note that the records that are returned do not match the records that are stored in the SQL Server table.