Using ADO to JOIN Worksheet with SQL Server Table

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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:

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

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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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