Query to two Access databases

Manuel Cavero

New Member
Joined
Feb 17, 2018
Messages
26
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?
VBA Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
122
Office Version
  1. 365
  2. 2019
Something is missing as I only see a connection to a single (One) DB.
 

Manuel Cavero

New Member
Joined
Feb 17, 2018
Messages
26
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

Board Regular
Joined
Jan 31, 2005
Messages
122
Office Version
  1. 365
  2. 2019
OK, I did see that; even so I am reasonably certain you need to open the 2nd DB.
 

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
122
Office Version
  1. 365
  2. 2019
You are welcome, thanks for the feedback.
 

Forum statistics

Threads
1,141,058
Messages
5,704,032
Members
421,323
Latest member
Exidous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top