Using ADO to JOIN Worksheet with SQL Server Table

[hatman]

So I have data in a Worksheet that I want to JOIN to data in a SQL Server Table. I got the syntax to work if I open a connection to the workbook, then specify the SQL Server Connection as a part of the Table name in the Query Statement:

Sub Join_PR_PO_RFQ_to_WF(WB As Excel.Workbook)

    Dim shtDest As Excel.Worksheet
    Dim shtPR_PO_RFQ As Excel.Worksheet
    
    Dim objRecordset As ADODB.Recordset
    Dim objConnection As ADODB.Connection
    Dim cnt As Long
    Dim sql As String
    Dim Conn_String As String
    
    Set shtDest = WB.Worksheets(6)
    Set shtPR_PO_RFQ = WB.Worksheets(5)
    
    shtDest.Name = "PR_PO_RFQ_JOIN_WF"

    Set objConnection = New ADODB.Connection
    Set objRecordset = New ADODB.Recordset
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & WB.FullName & _
            ";Extended Properties=""Excel 8.0;HDR=Yes;"";"
            
    Conn_String = "[odbc;Driver={SQL Server};" & _
        "Server=****;Database=****;" & _
        "UID=****;PWD=****]."
   
    sql = "SELECT * FROM [" & shtPR_PO_RFQ.Name & "$] a INNER JOIN " & Conn_String & Table_POWorkflow & " b ON " & _
        "a.PR_Purchase_Requisition = b.OrderNum"
        

    objRecordset.Open sql, objConnection, adOpenStatic, adLockOptimistic, adCmdText
        
    For cnt = 1 To objRecordset.Fields.Count
    
        shtDest.Cells(1, cnt).Value = objRecordset.Fields(cnt - 1).Name
    
    Next cnt
        
    shtDest.Range("A2").CopyFromRecordset objRecordset
    
    objRecordset.Close
    Set objRecordset = Nothing
    
    objConnection.Close
    Set objConnection = Nothing
    
    Set shtDest = Nothing
    Set shtPR_PO_RFQ = Nothing
    
End Sub

However, I already have a connection to the SQL Server Database, since I am performing a variety of transactions on numerous tables there. In that light, it seems wasteful to establish a second connection. So I am trying to reverse the logic a little here, and I can't get the syntax correct:

Sub Join_PR_PO_RFQ_to_WF_2(WB As Excel.Workbook)

    Dim shtDest As Excel.Worksheet
    Dim shtPR_PO_RFQ As Excel.Worksheet
    
    Dim objRecordset As ADODB.Recordset
    Dim objConnection As ADODB.Connection
    Dim cnt As Long
    Dim sql As String
    Dim Conn_String As String
    
    Set shtDest = WB.Worksheets(6)
    Set shtPR_PO_RFQ = WB.Worksheets(5)
    
    shtDest.Name = "PR_PO_RFQ_JOIN_WF"

    Set objConnection = New ADODB.Connection
    Set objRecordset = New ADODB.Recordset
    
    objConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
                          "User ID=****;Password=****;" & _
                          "Initial Catalog=****;Server=****"
            
'    Conn_String = "[Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
        "DriverId=790;" & _
        "DBQ=" & WB.FullName & "]."
        
    Conn_String = "[Provider=MSDASQL;DSN=Excel Files;DBQ=" & WB.FullName & ";HDR=Yes]."
   
    sql = "SELECT * FROM " & Conn_String & "[" & shtPR_PO_RFQ.Name & "$] a INNER JOIN " & Table_POWorkflow & " b ON " & _
        "a.PR_Purchase_Requisition = b.OrderNum"
        

    objRecordset.Open sql, objConnection, adOpenStatic, adLockOptimistic, adCmdText
        
    For cnt = 1 To objRecordset.Fields.Count
    
        shtDest.Cells(1, cnt).Value = objRecordset.Fields(cnt - 1).Name
    
    Next cnt
        
    shtDest.Range("A2").CopyFromRecordset objRecordset
    
    objRecordset.Close
    Set objRecordset = Nothing
    
    objConnection.Close
    Set objConnection = Nothing
    
    Set shtDest = Nothing
    Set shtPR_PO_RFQ = Nothing
    
End Sub

Any help in correcting this syntax is appreciated. In the meantime, to get around this wall, without connecting to SQL Server multiple times, I will probably simply bring the entire table to the local workbook and perform the JOIN from there... seems wasteful and cludgey, though.