ACC97: Outer Join Returns Wrong Records with ODBC Tables vs. Local Tables

Article translations Article translations
Article ID: 225422 - View products that this article applies to.
This article was previously published under Q225422
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all

On This Page

SYMPTOMS

You may get different results running a query that contains a WHERE clause and an outer join to another query on a local table than you get on an linked ODBC table. This happens even though the local table and the linked table contain the same data.

CAUSE

When the tables are local, the WHERE clause is processed before the outer join. However, when the tables are linked, the queries are combined into a single SQL statement and the WHERE clause is processed after the outer join.

RESOLUTION

To prevent this problem, the version of Microsoft Jet must be 3.51.2723.0 or later. To obtain a new version of Jet, install Microsoft Jet 3.51 Service Pack 2.

STATUS

This problem was corrected in Microsoft Jet 3.51 Service Pack 2.

MORE INFORMATION

For additional information about Microsoft Jet 3.51 Service Pack 2, please see the following article in the Microsoft Knowledge Base:
172733 Updated Version of Microsoft Jet 3.5 Available for Download

Steps to Reproduce Behavior

  1. Create a new database with the following tables and queries:
       Table: Table1
       -----------------
       Field Name: a
       Data Type: Number
    
       Field Name: b
       Data Type: Text
    
       Table: Table2
       -----------------
       Field Name: a
       Data Type: Number
    
       Field Name: c
       Data Type: Text
    
       Query: Query1
       -------------
       Field: a
       Table: Table2
    
       Field: c
       Table: Table2
       Criteria: "x"
    
       Query: Query2
       ------------------------------
       Join: Table1.[a] -> Query1.[a]
    
       Field: a
       Table: Table1
    
       Field: b
       Table: Table1
    
       Field: c
       Table: Query1
    					
  2. Add the following records to Table1:
       a     b
       -------
       1     a
       2     b
       3     c 
    					
  3. Add the following records to Table2:
       a     b
       -------
       1     x
       2     y
       3     z 
    					
  4. Select Query2, and then click Open.

    Note that there are three records.
  5. Export Table1 and Table2 to a SQL Server.
  6. In the Access database, rename Table1 and Table2 to Table1_local and Table2_local, respectively.
  7. Create a link to Table1 and Table2 on the SQL Server. You do not need to select a unique record identifier.
  8. Rename the linked tables to Table1 and Table2.
  9. Select Query2, and then click Open.

    Note that there is only one record.

Properties

Article ID: 225422 - Last Review: October 20, 2013 - Revision: 3.1
APPLIES TO
  • Microsoft Access 97 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB225422

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com