shorten connection time <<Macro Tweak needed
 

Hello -

I have the following code which opens up a SQL connection and then
dumps the data into Excel. Everything works fine, except that if
there is no ODBC connection found/made after 10 seconds, I just want
the "wating period" to end.

It seems that changing the ConnectionTimeout and CommandTimeout to a
lower value doesn't seem to do the job. No matter what I change these
values to, the process time for this macro is still 60 seconds.


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=00.00.000.000;Initial Catalog=xxdatadb;User Id=xx;Password=xx;"
cnn1.ConnectionTimeout = 60
' cnn1.Open
On Error Resume Next
cnn1.Open
On Error GoTo 0


If cnn1.State < adStateOpen Then
Debug.Print "Connection failed!"
Exit Sub
End If

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

x = 0

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

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("A1").CopyFromRecordse t rs
End If

rs.Close
cnn1.Close
End Sub


Thanks for any insight someone can provide!
Dan