Results 1 to 4 of 4

Thread: ADO Sql query across two databases
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2015
    Posts
    512
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ADO Sql query across two databases

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ADO Sql query across two databases

    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;

  3. #3
    Board Regular
    Join Date
    Feb 2015
    Posts
    512
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ADO Sql query across two databases

    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 by bradyboyy88; Sep 5th, 2019 at 10:11 PM.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,865
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ADO Sql query across two databases

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •