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

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

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

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!