Retrieve Data from SQL server

[AnilPullagura]

Excel 2010
SQL Server 2005

Hello all,

I have written a macro that retrieves data from SQL server to Excel. This is simple Select query which uses the data in column A of the excel and runs it against the SQL server DB. I have more than a thousand rows of data in column A.

I figured out that the macro works this way who suits for me now. It takes the data in Cell "A2", fires the query against the SQL server, fetches data, populates it in the Excel columns( B to E) and moves on to the next cell "A3". It again fires the same query and fetches data and populates in the spreadsheet.

Challenges:
Since there are 1000 cells of data in Column A, the query is triggered 1000 times and subsequently 1000 hits to the server.

Requirement:
I want all the 1000 cells of data to hit the SQL DB in one query and then fetch data to spreadsheet. This will increase the performance of the macro and the server is hit only once.

My thoughts:

• We need to create a temp table using VBA and then store this 1000 cells of data in that temp table, fire the query and drop the temp table
• Concantenate all the Data Range of Column A and then fire a single query to fetch data for the concatenated data. I dont know how to concatenate data using VBA

Please find the below code:

Sub retreiveData()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 'Create a connection object.
Dim cnrptgprod As ADODB.Connection
Set cnrptgprod = New ADODB.Connection
Sheets("Data").Select
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "Provider=SQLOLEDB.1;"
'Connect to the database on the server.
strConn = strConn & "Data Source=DP-DBREPLICATE;INITIAL CATALOG=facippr0;"
'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"
'Now open the connection.
cnrptgprod.Open strConn
' Create a recordset object.
Dim rsrptgprod As ADODB.Recordset
Dim i As Integer
Set rsrptgprod = New ADODB.Recordset
 
With rsrptgprod
    .ActiveConnection = cnrptgprod
    lastrw = Range("A1").End(xlDown).Row
    For i = 2 To lastrw
 
    .Open "SELECT MAX(CDML_CUR_STS), MAX(CDML_SEQ_NO), SUM(CDML_CHG_AMT), C.WQDF_DESC FROM facippr0.dbo.CMC_CDML_CL_LINE A WITH(NOLOCK) INNER JOIN dbo.NWX_WMHS_MSG_HIST B WITH(NOLOCK) ON A.CLCL_ID = B.WMHS_MESSAGE_ID INNER JOIN dbo.NWX_WQDF_QUEUE_DEF C WITH(NOLOCK) ON B.WQDF_QUEUE_ID = C.WQDF_QUEUE_ID WHERE B.WMHS_ROUTING_DTM = (SELECT MAX(WMHS_ROUTING_DTM) FROM  facippr0.dbo.NWX_WMHS_MSG_HIST WHERE WMHS_MESSAGE_ID = A.CLCL_ID) AND CLCL_ID = '" & Range("A" & i) & "'" & "GROUP BY C.WQDF_DESC"
        Do Until rsrptgprod.EOF
           Sheets("Data").Cells(i, 2) = .Fields(0)
           Sheets("Data").Cells(i, 3) = .Fields(1)
           Sheets("Data").Cells(i, 4) = .Fields(2)
           Sheets("Data").Cells(i, 5) = .Fields(3)
            .MoveNext
 
        Loop
        .Close
     Next i
 
End With
cnrptgprod.Close
Set rsrptgprod = Nothing
Set cnrptgprod = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Thanks Much,
Anil Pullagura