ACC2000: Microsoft Jet Does Not Pass WHERE Clause to ODBC Data Source (282604)


The information in this article applies to:

  • Microsoft Access 2000 Service Release 1 (SR-1)
  • Microsoft Data Access Components 2.6
This article was previously published under Q282604
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

When you use Access 2000 Service Release 1 (SR-1) with Microsoft Jet 4.0 Service Pack 5 (SP5) to query tables linked from two separate databases on the same server, you may find that Jet does not pass the WHERE clause parameters to the ODBC data source when both of the following are true:
  • The query creates a join between the two tables.
  • The query contains a WHERE clause that uses both the OR and the AND operators.
You may find that query performance is extremely slow when you query large sets of data under these conditions. Saving the password when you link the tables may or may not affect performance. This only affects Msjet40.dll version 4.0.4431.x

CAUSE

Jet does not send the WHERE clause for one or for both queried tables. This causes the server to return all rows to the client.

RESOLUTION

To resolve this problem, install the latest Jet 4.0 service pack. For additional information about how to obtain and how to install the latest Jet 4.0 service pack, click the following article number to view the article in the Microsoft Knowledge Base:

239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first fixed in Jet 4.0 Service Pack 6 (SP6).

MORE INFORMATION

Steps to Reproduce the Behavior

  1. On a computer that is running Microsoft SQL Server 7.0, use Enterprise Manager to create a new database called pubs2.
  2. Open Enterprise Manager.
  3. Expand the server group for the local server.
  4. Right-click Databases, and then select the option to create a new database (accept all defaults).
  5. Change the new database name to pubs2, and then close the dialog box.
  6. Copy the Authors table from pubs to pubs2, either by using Enterprise Manager or by executing the following SELECT statement in Query Analyzer:

    SELECT * INTO pubs2..authors FROM pubs..authors

  7. Create a new database in Access called Test.mdb.
  8. Create a new system DSN TestPubs to SQL 7.0 server. Change the default database to pubs and supply a valid userid and password.
  9. Create a new system DSN TestPubs2 to SQL 7.0 server. Change the default database to pubs2 and supply a valid userid and password.
  10. Link pubs.titleauthor to Test.mdb by using "TestPubs." Do not save the password.
  11. Link pubs2.authors to Test.mdb by using "TestPubs2." Do not save the password.
  12. Create the following new query in Design view in Test.mdb:

    SELECT dbo_authors.au_id, dbo_authors.au_lname, dbo_titleauthor.title_id
    FROM dbo_authors INNER JOIN dbo_titleauthor ON dbo_authors.au_id = dbo_titleauthor.au_id
    WHERE (dbo_authors.au_lname = "Ringer" AND dbo_titleauthor.title_id = "PS2106") OR dbo_authors.au_lname = "White"

  13. Save the query as qSel2Authors.
  14. Quit Access.
  15. Start tracing from the ODBC Data Sources tool in Control Panel.
  16. Open Test.mdb, and then execute qSel2Authors.
  17. Quit Access.
  18. Stop tracing in the ODBC Data Sources tool in Control Panel.
  19. Open the trace log in Microsoft Word 2000.
  20. Search for au_id in the trace log.

    Note the SELECT statement sent and records returned.
  21. Search for title_id in the trace log.

    Note the SELECT statement sent and records returned.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbbug kbfix KB282604 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.