Macro to call database table needs to process faster

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hello all -

I have a macro which calls out to a SQL database table and populates
into an Excel sheet. There are 1700 records in the SQL table, but it
is taking close to 2 minutes to populate the records back into Excel.
The slowdown seems to be in the loop that was created.

Any ideas on how to this macro can process faster?


Private Sub Workbook_Open()
Dim cnn1 As ADODB.Connection
Dim runspcmd As ADODB.Command
Dim x As Integer

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=sqloledb;Data
Source=000.00.000.000;Initial Catalog=brdatadb;User
Id=stuff;Password=stuff;"
cnn1.ConnectionTimeout = 300

cnn1.Open

Set runspcmd = New ADODB.Command
runspcmd.ActiveConnection = cnn1
runspcmd.CommandTimeout = 120

x = 0

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient

runspcmd.CommandText = "select vendor_code, vendor_name from vendor
order by vendor_name "
rs.Open runspcmd

For x = 1 To rs.RecordCount

If Not rs.EOF Then
Me.Worksheets.Item(2).Cells(x + 1, 1).Value = rs("vendor_name")
Me.Worksheets.Item(2).Cells(x + 1, 2).Value = rs("vendor_code")

End If
rs.MoveNext
Next

rs.Close
cnn1.Close
End Sub


Thanks for any assistance!
Dan
 
runspcmd.CommandText = "select vendor_name, vendor_code from " & _
" vendor order by vendor_name "
rs.Open runspcmd

if not rs.eof then
Me.Worksheets.Item(2).range("A2").copyfromrecordset rs
end if
 
runspcmd.CommandText = "select vendor_name, vendor_code from " & _
" vendor order by vendor_name "
rs.Open runspcmd

if not rs.eof then
Me.Worksheets.Item(2).range("A2").copyfromrecordset rs
end if

--
Tim Williams
Palo Alto, CA

















- Show quoted text -
Click to expand...

Perfect!! Thanks so much for helping out this rookie.

Dan
 
Back
Top