ADO Sql query across two databases

[bradyboyy88]

I have a function I put together which allows you to connect to an ado database. I am having trouble figuring out how to adapt this to allow queries across multiple databases considering the connection string only includes Data Source parameter with one database directory to link to. If this is possble what would a sample sql query look like to select and intersect two tables in two seperate databases?

Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
 
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
    
RetryConnection:
    Sleep 100
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database


20       oConn.Open sConn


    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub

'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String) As ADODB.Recordset
       
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.LockType = adLockBatchOptimistic
    
    Sleep 100
    DoEvents
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
        
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs
    
    Set oRs = Nothing
    
End Function

 

[Legacy 98055]

It might be helpful if you would post your query text.
I'm assuming that you are using a join on tables in several different databases?
If so, assuming an ADO connection to Database1.accdb, use syntax such as the following:

SELECT DB1Table.DB1Field, DB2Table.DB2Field
FROM DB1Table
INNER JOIN [C:\Users\tom\Desktop\Database2.accdb].DB2Table 
ON DB1Table.DB1ID = DB2Table.DB2ID;

 

[bradyboyy88]

Your example is basically what I will be doing (I will be using inner joins , unions, etc. in the future using this technique). Do the database tables need to be linked in the access database? I would assume so because how would the ado connection create a connection to the second database if its not. Also, if I do link the databases does everytime I do a query on the main database only does it load the other database by default and slow down the connection when doing so? I cannot find much info on this topic online.

Thanks
Shaun

 

[Legacy 98055]

No linking needed. This, [C:\Users\tom\Desktop\Database2.accdb].DB2Table, is all the dbengine needs to create an implicit connection I suppose. All I know is that is works and has come in handy quite often for me.