Macro to call database table needs to process faster
 

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

Macro to call database table needs to process faster
 

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").copyfromrecordse t rs
end if

--
Tim Williams
Palo Alto, CA


"Dan" wrote in message oups.com...
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

Macro to call database table needs to process faster
 

On Feb 28, 4:47 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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").copyfromrecordse t rs
end if

--
Tim Williams
Palo Alto, CA



"Dan" wrote in ooglegroups.com...
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- Hide quoted text -

- Show quoted text -

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

Dan