stop macro if no SQL connection found

[Dan]

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").CopyFromRecordset rs
End If

rs.Close
cnn1.Close
End Sub

Thanks for any assistance!
Dan

 

[Tim Williams]

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

 

[NickHK]

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/mi...ordSet+is+open?&rnum=1&hl=en#f273fcb85898b202

NickHK

 

[Tim Williams]

Good to know - thanks.

Tim

 

[Dan]

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
















- Show quoted text -

Thanks Tim, works great!