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.

No records returned in query between an Access 2000 table and a linked Access 97 table


View products that this article applies to.

This article was previously published under Q244137
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top


Symptoms

No records are returned for a query that joins a Microsoft Access 2000 (Jet 4.0) table to a linked Microsoft Access 97 (Jet 3.5) table. This happens even though you are certain that there are matching records.

↑ Back to the top


Cause

This behavior can occur only when all the following conditions are true:
  • The query contains an Access 2000 table and a linked Access 97 table.
  • The two tables are joined in a one-to-one relationship (Type 1).
  • In each table, the linked field has a data type of Text and is indexed.

↑ Back to the top


Resolution

Instead of a join, use a criteria with the keyword Like that compares one field to the other field in the linked tables. For instance, to correct the behavior in the example in the "Steps to Reproduce Behavior" section later in this article, follow these steps:
  1. Highlight the join between the two tables to select it.
  2. Press DELETE.
  3. In the Criteria field under CustomerID from the Customers table, type the following:
    Like [Customers1].[CustomerID]

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

↑ Back to the top


More information

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Steps to Reproduce Behavior

  1. Place an unchanged copy of the sample database Northwind.mdb that is included with Access 97 on your desktop. Do not convert it to Access 2000.
  2. In Access 2000, open the sample database Northwind.mdb that is included with Access 2000.
  3. On the File menu, point to Get External Data, and then click Link Tables
  4. In the Link dialog box, browse to the Access 97 version of the sample database Northwind.mdb, click Northwind.mdb, and then click Link.
  5. In the Link Tables dialog box, click Categories and Customers, and then click OK. Note that two new linked tables, Categories1 and Customers1, appear in the Database window.
  6. Create a new query that includes both the Customers table from the sample database Northwind.mdb 2000 and the linked Customers1 table. Note that the tables are automatically joined on the CustomerID field (Primary Key in both, and Text in both).
  7. Drag the CustomerID field from both tables to the query design grid.
  8. Run the query.

    Note that no records are returned.
  9. Create a second query that includes the Categories table from the sample database Northwind.mdb 2000 and the linked Categories1 table. Note that the tables are automatically joined on the CategoryID field (Indexed in both, and Numeric in both).
  10. Drag the CategoryID field from both tables to the query grid.
  11. Run the query.

    Note that all records are returned. In this case, because the joined fields are a Number data type instead of a Text data type, records are returned.

↑ Back to the top


Keywords: KB244137, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 244137
Revision : 4
Created on : 1/25/2007
Published on : 1/25/2007
Exists online : False
Views : 259