FIX: Heterogeneous Join Between Jet 4 and Jet 3.x Tables Returns No Records (254130)

MORE INFORMATION

Steps to Reproduce Behavior

1. In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default.
2. 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.
   
   
3. 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.
   
   
4. 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.
   
   
5. If you see zero records, uncomment the second assignment to the SQL variable. This causes the Join to occur on a non-indexed field.
6. Re-run the application and you should see 91 records returned.