Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear option buttons **MACRO TWEAK** | Excel Programming | |||
Newby Needs minor tweak on this VBA Macro code for Excel | Excel Programming | |||
Can someone please tweak my Macro? | Excel Discussion (Misc queries) | |||
Macro needed; save a file two (or more places) at the same time | Excel Programming | |||
Help needed coding a macro to adjust the source of information each time it is run | Excel Programming |