Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB Connections
I'm using the following code to retrieve 4 records from a MS SQL Server 2000
backend db. What I don't understand is why it takes 4 minutes to run this in Excel, but only 25 seconds to run the SQL in Query Analyzer. Sure Excel has to make a DSNless connection, but the problem seems to lie in creating the recordset and then counting the records retrieved from in the recordset. The code seems to freeze in the rs.movefirst commands. I've tried replacing the rs.movefirst and loop with a simple intCtr = rs.recordcount call, and while that works it still takes a few minutes to run. Since I have several of these calls to make, I'm beginning to think I should find another way to do this. Any thoughts or ideas would be greatly appreciated. TIA Mark Private Sub Get_AI_Data(ByVal dtEnd As Date) 'retrieve Metrics_Results On Error GoTo Err_Handler Dim Cnxn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim strCnxn As String Dim strMsg As String Dim strSTC As String Dim strSQL As String Dim strFL As String Dim strAI As String Dim intCtr As Integer Dim intAddRows As Integer 'clear the current report results and paste the results Sheets("Metrics Data").Visible = True Sheets("Metrics Data").Select Sheets("Metrics Data").Range("Metrics_Results").ClearContents Set Cnxn = New ADODB.Connection With Cnxn .ConnectionString = "Provider=SQLOLEDB;Data Source=MY_SEVER;Initial Catalog=MY_DB;User Id=MY_USER;Password=MY_PWD;" .ConnectionTimeout = 0 .Open End With Set cmd = New ADODB.Command With cmd .ActiveConnection = Cnxn .CommandText = "SELECT * FROM dbo.fnMetrics_2006('" _ & Format(dtEnd, "mm/dd/yyyy") & "')" .CommandType = adCmdText .Execute End With 'SQL to call from db Set rs = New ADODB.Recordset Set rs.ActiveConnection = Cnxn rs.Open cmd 'if no records retrieved exit If rs.EOF Then GoTo Exit_Routine End If 'count the retrieved records rs.MoveFirst Do Until rs.EOF intCtr = intCtr + 1 rs.MoveNext Loop rs.MoveFirst 'size the report range RowInserter: If Range("Metrics_Results").Rows.Count < intCtr Then intAddRows = intCtr - Range("Metrics_Results").Rows.Count Range(Cells(Range("Metrics_Results").Row + 1, Range("Metrics_Results").Column), _ Cells(Range("Metrics_Results").Row + intAddRows, Range("Metrics_Results").Column)).Select Selection.EntireRow.Insert ElseIf Range("Metrics_Results").Rows.Count intCtr _ And Range("Metrics_Results").Rows.Count 2 Then Range(Cells(Range("Metrics_Results").Row + 1, Range("Metrics_Results").Column), _ Cells(Range("Metrics_Results").Row + Range("Metrics_Results").Rows.Count - 2, Range("Metrics_Results").Column)).Select Selection.EntireRow.Delete GoTo RowInserter End If 'paste the results If Not rs.EOF Then Sheets("Metrics Data").Range("Metrics_Results").CopyFromRecordset rs End If Sheets("Metrics Data").Range("A1").Select Exit_Routine: rs.Close Cnxn.Close Set rs = Nothing Set Cnxn = Nothing Exit Sub Err_Handler: strMsg = Err.Description MsgBox "The following error occurred getting the data:" & vbCrLf & vbCrLf & strMsg GoTo Exit_Routine End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB Connections
Just a possible suggestion, try specifying a client side cursor:
Add the following line to your connection "With" clause: .CursorLocation = adUseClient HTH/ "MChrist" wrote: I'm using the following code to retrieve 4 records from a MS SQL Server 2000 backend db. What I don't understand is why it takes 4 minutes to run this in Excel, but only 25 seconds to run the SQL in Query Analyzer. Sure Excel has to make a DSNless connection, but the problem seems to lie in creating the recordset and then counting the records retrieved from in the recordset. The code seems to freeze in the rs.movefirst commands. I've tried replacing the rs.movefirst and loop with a simple intCtr = rs.recordcount call, and while that works it still takes a few minutes to run. Since I have several of these calls to make, I'm beginning to think I should find another way to do this. Any thoughts or ideas would be greatly appreciated. TIA Mark Private Sub Get_AI_Data(ByVal dtEnd As Date) 'retrieve Metrics_Results On Error GoTo Err_Handler Dim Cnxn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim strCnxn As String Dim strMsg As String Dim strSTC As String Dim strSQL As String Dim strFL As String Dim strAI As String Dim intCtr As Integer Dim intAddRows As Integer 'clear the current report results and paste the results Sheets("Metrics Data").Visible = True Sheets("Metrics Data").Select Sheets("Metrics Data").Range("Metrics_Results").ClearContents Set Cnxn = New ADODB.Connection With Cnxn .ConnectionString = "Provider=SQLOLEDB;Data Source=MY_SEVER;Initial Catalog=MY_DB;User Id=MY_USER;Password=MY_PWD;" .ConnectionTimeout = 0 .Open End With Set cmd = New ADODB.Command With cmd .ActiveConnection = Cnxn .CommandText = "SELECT * FROM dbo.fnMetrics_2006('" _ & Format(dtEnd, "mm/dd/yyyy") & "')" .CommandType = adCmdText .Execute End With 'SQL to call from db Set rs = New ADODB.Recordset Set rs.ActiveConnection = Cnxn rs.Open cmd 'if no records retrieved exit If rs.EOF Then GoTo Exit_Routine End If 'count the retrieved records rs.MoveFirst Do Until rs.EOF intCtr = intCtr + 1 rs.MoveNext Loop rs.MoveFirst 'size the report range RowInserter: If Range("Metrics_Results").Rows.Count < intCtr Then intAddRows = intCtr - Range("Metrics_Results").Rows.Count Range(Cells(Range("Metrics_Results").Row + 1, Range("Metrics_Results").Column), _ Cells(Range("Metrics_Results").Row + intAddRows, Range("Metrics_Results").Column)).Select Selection.EntireRow.Insert ElseIf Range("Metrics_Results").Rows.Count intCtr _ And Range("Metrics_Results").Rows.Count 2 Then Range(Cells(Range("Metrics_Results").Row + 1, Range("Metrics_Results").Column), _ Cells(Range("Metrics_Results").Row + Range("Metrics_Results").Rows.Count - 2, Range("Metrics_Results").Column)).Select Selection.EntireRow.Delete GoTo RowInserter End If 'paste the results If Not rs.EOF Then Sheets("Metrics Data").Range("Metrics_Results").CopyFromRecordset rs End If Sheets("Metrics Data").Range("A1").Select Exit_Routine: rs.Close Cnxn.Close Set rs = Nothing Set Cnxn = Nothing Exit Sub Err_Handler: strMsg = Err.Description MsgBox "The following error occurred getting the data:" & vbCrLf & vbCrLf & strMsg GoTo Exit_Routine End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADODB Connection | Excel Worksheet Functions | |||
ADODB SQL help | Excel Programming | |||
Problems with ADODB | Excel Programming | |||
0 with ADODB Recordset | Excel Programming | |||
ADODB | Excel Programming |