D
Dan
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").CopyFromRecordset rs
End If
rs.Close
cnn1.Close
End Sub
Thanks for any insight someone can provide!
Dan
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").CopyFromRecordset rs
End If
rs.Close
cnn1.Close
End Sub
Thanks for any insight someone can provide!
Dan