Microsoft KB Archive/254130

= FIX: Heterogeneous Join Between Jet 4 and Jet 3.x Tables Returns No Records =

Article ID: 254130

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition
 * Microsoft OLE DB Provider for Jet 4.0

-



This article was previously published under Q254130



SYMPTOMS
When performing a query that joins a table in an Microsoft Access 2000 (Jet 4.0) database with a table in a database from an earlier version of Access (or Jet), you get zero (0) records returned. This behavior only occurs with indexed Text (VarChar or Char) columns.



CAUSE
The data stored in a text field index is different in Microsoft Jet 4.0 than in earlier versions of the Jet database engine. Therefore, comparisons fail.



RESOLUTION
To resolve the problem, do one of the following:
 * Remove the indexes.
 * Join on a numeric field, such as an ID column.
 * Import the table into the Jet 4.0 database.
 * Upgrade to Microsoft Jet 4.0 SP4.

NOTE: The query optimizer no longer performs an Index Join on tables in older database formats.



STATUS
This bug has been fixed.



Steps to Reproduce Behavior
 In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default. On the Project menu, select References, and add a reference to the following type libraries: Microsoft ActiveX Data Objects 2.1 Library

Microsoft DAO 3.6 Object Library

The code below has sections for both DAO and ADO. If you do not have both type libraries available, comment out the section that does not apply.

  Add a Command button and the following code to the default form: Private Sub Command1_Click Dim SQL As String, dbPath As String SQL = "SELECT Cust40.* FROM Customers AS Cust40 INNER JOIN [nwind.mdb].Customers AS Cust3x ON Cust40.CustomerID = Cust3x.CustomerID" ' SQL = "SELECT Cust40.* FROM Customers AS Cust40 INNER JOIN [nwind.mdb].Customers AS Cust3x ON Cust40.Phone = Cust3x.Phone" dbPath = "C:\Program Files\Microsoft Office\Office\Samples\NorthWind.MDB" ' ' DAO Test ' Dim db As dao.Database, rs As dao.Recordset Set db = DBEngine(0).OpenDatabase(dbPath) Set rs = db.OpenRecordset(SQL) If Not rs.EOF Then rs.MoveLast Debug.Print "DAO: There are"; rs.RecordCount; "records." rs.Close db.Close ' ' ADO Test ' Dim cn As ADODB.Connection, rs2 As ADODB.Recordset Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath Set rs2 = New ADODB.Recordset rs2.Open SQL, cn, adOpenKeyset, adLockReadOnly, adCmdText If Not rs2.EOF Then rs2.MoveLast Debug.Print "ADO: There are:"; rs2.RecordCount; "records." rs2.Close cn.Close End Sub NOTE: You might need to adjust the paths for the Microsoft Jet 4.0 Northwind.mdb and the Microsoft Jet 3.x nwind.mdb files assigned to the dbPath and SQL variables.

 Run the application and click the Command button. The results are: If you have Microsoft Jet 4.0 SP3 or earlier, you receive zero (0) records from the Join.

If you have Microsoft Jet 4.0 SP4 or late, you receive 91 records from the Join.

 If you see zero records, uncomment the second assignment to the SQL variable. This causes the Join to occur on a non-indexed field.</li> Re-run the application and you should see 91 records returned.</li></ol>

NOTE: This problem can also be reproduced in Microsoft Access 2000 by linking to an Access 97 table and creating a query which joins the linked table with a native table.

Additional query words: jet 4 0 3 5 x

Keywords: kbbug kbfix kbjet KB254130

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.