We help IT Professionals succeed at work.

Query Two Tables in seperate databases

Friggin_Lazy
Friggin_Lazy asked
on
Hello Everyone
   Say i have two Access databases:
        Database1.accdb
        Database2.accdb
   I'm within Excel i'm using VBA:
     I can connect to one database by using:

          Set objConnection = New ADODB.Connection
          objConnection.Mode = adModeRead
          objConnection.Open connectionString
         sqlString = "SELECT * FROM Table1"

         Set objRecordSet = objConnection.Execute(sqlString)

  however, is it possible to connect to two seperate databases...
  to do a join query?

thanks in advance..
Comment
Watch Question

It would be easier to do the join query in either Access database and then, open the join query from Excel.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
If you could do this in Access, it would be much simpler - just link both tables, and then you can work with them as needed. Using ADO from Excel, you'd not be able to join two tables from different data sources.
Top Expert 2014

Commented:
Create a third database that has these two tables attached to it.  Then you can run your join query against this new database.  You can create the database and attach tables manually or with ADOX.
Most Valuable Expert 2011
Top Expert 2011
Commented:
For example:
Sub GetDataFromTwoDatabases()
' Sample demonstrating how to return a recordset from two databases
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim varData           As Variant
   Set cn = New ADODB.Connection
   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      ' connect to database1
      .ConnectionString = "Data Source=C:\Test\Database1.accdb;"
      .Open
   End With
   ' Select query with Table1 in both databases
   strQuery = "SELECT tblA.[Field1] FROM [Table1] tblA INNER JOIN (SELECT * FROM `C:\Test\Database2.accdb`.[Table1]) As tblB ON tblA.[ID] = tblB.[ID]"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText

   ' do whatever you need with recordset here
   ActiveSheet.Range("A2").CopyFromRecordset rst
   rst.Close
   Set rst = Nothing
   cn.Close
   Set cn = Nothing
End Sub

Open in new window

Author

Commented:
Sorry for my delay.   This solution did it.. THANK YOU!