Query to two Access databases

[Manuel Cavero]

Hi everyone
I'm working in a code that need retrieves data from two Access databases, but unfortunately it doesn't works What the matter?

Dim cn    as adodb.connection
Dim Qr    as string
Dim rs    as adodb.recordset
'Opens the conection
set cn = new adodb.connection
with cn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & DB1Path
    .CursorLocation = adUseClient
    .open
end with
'Define the Query
Qr = _
    "SELECT " & _
           Table1 & ".Field1, " & Table1 & ".Field2, & _
           Table2 & ".Field1, " & Table2 & ".Field2" & _
    "FROM " & _
        Table1 & " " & _
    "INNER JOIN " & _
        "[" & DB2Path & "]." & Table2Name & " " & _
        "ON " & Table1 & ".Field1 = " & Table2 & ".Field1" & " " & _
        "AND " & Table1 & ".Field1 >= #" & InitialDate & "# " & _
    "ORDER BY " & Table1 & ".Field1 " & AND " & Table2 & ".Field1"
'Opens the recordset
set rs = new adodb.recordset
With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Source = iQuery
    .Open
End With

 

[RayFrye]

Something is missing as I only see a connection to a single (One) DB.

 

[Manuel Cavero]

Hi Ray:

You're right.

In fact there is a connection to just one DB. But if you look carefully to the query, there is a Join to the second DB

 

[RayFrye]

OK, I did see that; even so I am reasonably certain you need to open the 2nd DB.

 

[Manuel Cavero]

Thanks a lot Ray! thats what I was looking for!

 

[RayFrye]

You are welcome, thanks for the feedback.