Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
stop macro if no SQL connection found
Try
on error resume next cnn1.Open on error goto 0 if cnn1.state<adStateOpen then debug.print "Connection failed!" exit sub end if Tim "Dan" wrote in message oups.com... Hello - I have the following macro and would like to end it if no SQL connection is found. The code process for about a minute before it will get a run-time error when no connection is present. I would like the macro to end with no warning boxes; basically invisible to the end user. 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=xxx;Password=xxx;" cnn1.ConnectionTimeout = 60 cnn1.Open 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 assistance! Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
stop macro if no SQL connection found
As the OP is using the default value (True) of the Options argument to
..Open, the connection is opened synchronously, so this works. However, the .State is actually a bit mask so you should check the .State with If (cnn1.State And adStateOpen) = adStateOpen Then 'Connection is open Else 'Connection is closed End If Read the help on the State Property, although the examples do not make this clear. This was recently pointed out in: http://groups.google.co.uk/group/mic...73fcb85898b202 NickHK "Tim Williams" <timjwilliams at gmail dot com wrote in message ... Try on error resume next cnn1.Open on error goto 0 if cnn1.state<adStateOpen then debug.print "Connection failed!" exit sub end if Tim "Dan" wrote in message oups.com... Hello - I have the following macro and would like to end it if no SQL connection is found. The code process for about a minute before it will get a run-time error when no connection is present. I would like the macro to end with no warning boxes; basically invisible to the end user. 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=xxx;Password=xxx;" cnn1.ConnectionTimeout = 60 cnn1.Open 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 assistance! Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
stop macro if no SQL connection found
Good to know - thanks.
Tim "NickHK" wrote in message ... As the OP is using the default value (True) of the Options argument to .Open, the connection is opened synchronously, so this works. However, the .State is actually a bit mask so you should check the .State with If (cnn1.State And adStateOpen) = adStateOpen Then 'Connection is open Else 'Connection is closed End If Read the help on the State Property, although the examples do not make this clear. This was recently pointed out in: http://groups.google.co.uk/group/mic...73fcb85898b202 NickHK |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
stop macro if no SQL connection found
On Feb 28, 9:57 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote: Try on error resume next cnn1.Open on error goto 0 if cnn1.state<adStateOpen then debug.print "Connection failed!" exit sub end if Tim "Dan" wrote in message oups.com... Hello - I have the following macro and would like to end it if no SQL connection is found. The code process for about a minute before it will get a run-time error when no connection is present. I would like the macro to end with no warning boxes; basically invisible to the end user. 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=xxx;Password=xxx;" cnn1.ConnectionTimeout = 60 cnn1.Open 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 assistance! Dan- Hide quoted text - - Show quoted text - Thanks Tim, works great! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return cells below stop when word found | Excel Discussion (Misc queries) | |||
Stop a loop when certain data is found | Excel Discussion (Misc queries) | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
Stop a Macro if no Data found | Excel Programming | |||
Stop the loop when found. | Excel Programming |