Retrieve Data from SQL Server

AnilPullagura

Board Regular
Joined
Nov 19, 2010
Messages
98
Hello Pros,

I have written a code to retrieve data from SQL Server to Excel but the performance is slow. The project goes like:

There are values in the Range A2:A, and for each of these values corresponding 3 values are to be retrieved for SQL DB. I did accomplish that, however the performance is slow because the code written by me will take value in A2, execute the query and retrieve data and dumps that record to B2,C2 and D2. Next the macro proceeds to A3 and performs the same routine. This will not consume time if the range is low(say 100 cells in Acolumn) but I now encountered that this macro will be used for 10000cells of data :):confused::( . I am happy for the success and at the same time lost in confusion.

Is there any method where all the values(A2:A) can be passed to the query and after retrieving the recordset, cant we populate them to Excel?
I am a novice in both SQL and VBA. Please check if my code below and let know the possibility of this.

Code:
[SIZE=3][FONT=Calibri]Sub enjoi()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.DisplayAlerts = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim cnrptgprod As ADODB.Connection<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set cnrptgprod = New ADODB.Connection<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Sheets("Data").Select<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim strConn As String<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]'Range("A7:V444").Clear<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strConn = "Provider=SQLOLEDB.1;"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strConn = strConn & "Data Source=rptgprod;INITIAL CATALOG=BpoReportData;"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]strConn = strConn & " INTEGRATED SECURITY=sspi;"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]cnrptgprod.Open strConn<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim rsrptgprod As ADODB.Recordset<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim i As Integer<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rsrptgprod = New ADODB.Recordset<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]With rsrptgprod<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   .ActiveConnection = cnrptgprod<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   lastrw = Range("A1").End(xlDown).Row<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   For i = 2 To lastrw   <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   .Open "SELECT MAX(CDML_CUR_STS), MAX(CDML_SEQ_NO), D.WQDF_DESC FROM facippr0.CMC_CLCL_CLAIM A WITH(NOLOCK) INNER JOIN facippr0.CMC_CDML_CL_LINE B WITH(NOLOCK) ON A.CLCL_ID = B.CLCL_ID INNER JOIN facippr0.dbo.NWX_WMHS_MSG_HIST C WITH(NOLOCK)ON A.CLCL_ID = C.WMHS_MESSAGE_ID INNER JOIN facippr0.dbo.NWX_WQDF_QUEUE_DEF D WITH(NOLOCK) ON C.WQDF_QUEUE_ID = D.WQDF_QUEUE_ID WHERE C.WMHS_ROUTING_DTM = (SELECT MAX(WMHS_ROUTING_DTM) FROM  facippr0.dbo.NWX_WMHS_MSG_HIST WHERE WMHS_MESSAGE_ID = B.CLCL_ID) AND A.CLCL_ID = '" & Range("A" & i) & "'" & "GROUP BY D.WQDF_DESC"    <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Do Until rsrptgprod.EOF<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Sheets("Data").Cells(i, 2) = .Fields(0)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Sheets("Data").Cells(i, 3) = .Fields(1)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Sheets("Data").Cells(i, 4) = .Fields(2)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           .MoveNext[/FONT][/SIZE][SIZE=3][FONT=Calibri] <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Loop<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .Close<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Next i[/FONT][/SIZE][SIZE=3][FONT=Calibri]  <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End With<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]cnrptgprod.Close<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set rsrptgprod = Nothing<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set cnrptgprod = Nothing<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.DisplayAlerts = True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
Thanks Much,
Anil
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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