Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Query of ODBC Linked Data Returns Unexpected Results

ACC2000: Query of ODBC Linked Data Returns Unexpected Results

View products that this article applies to.

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

↑ Back to the top

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.

↑ Back to the top

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)

↑ Back to the top

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.

↑ Back to the top

More information

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.

↑ Back to the top

Keywords: KB302968, kbprb

↑ Back to the top

Article Info
Article ID : 302968
Revision : 2
Created on : 6/25/2004
Published on : 6/25/2004
Exists online : False
Views : 264