ADO Sql query across two databases

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
562
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?

Code:
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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:

Code:
SELECT DB1Table.DB1Field, DB2Table.DB2Field
FROM DB1Table
INNER JOIN [C:\Users\tom\Desktop\Database2.accdb].DB2Table 
ON DB1Table.DB1ID = DB2Table.DB2ID;
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top