ADO: Connection.State unreliable ?

[Toff]

Hello there,

I'm writing a generic error handler where I want to
test the connection state to my databases. (SQLServer 7
via OLEdb)

Unfortunately (perhaps because I'm using the data
environment?) the 'State' property of the connection object
does'nt change whenever the database isn't accessible.

I have also tried to use the "Connection Status" member
of the Connection.Properties's collection, but in some case
this property doesn't exist.

So how do I detect a connection failure to my databases
when using ADO?

TIA.

[DrewDog_21]

If the connection was set at a time when the database was accesible, then the State property should always show a valid connection even if the database is not available at the present time. It won't show an unconnected status until you close it. That's not just in the Data Environment, either - it seems to be the case with ADO recordsets too.

I have a program where I store the connection string in an .ini file that is created during the setup process. When the program launches, I test the connection liek this:


Private Sub Form_Load()

Dim Text As String
Dim cnTest As New adodb.Connection

On Error GoTo Error_INI

'open file and look for errors
Open App.Path & "\Sicc.ini" For Input As #1

Line Input #1, Text
If Text <> "[MyProgram]" Then
GoTo OpenConfig
End If

Line Input #1, Text
If Text <> "CONFIGURACION VALUES. DO NOT DELETE OR MODIFY THIS FILE" Then
GoTo OpenConfig
End If

Line Input #1, Text
If Not IsNumeric(Text) Then
GoTo OpenConfig
Else
FirstYear = Text
End If

Line Input #1, Text
Line Input #1, Text
If Text <> "[ADO CONNECTION STRING]" Then
GoTo OpenConfig
End If

'test ADO connection. Here if the database is not available, and error will occur
Line Input #1, Text
cnADO = Text 'assigns connection string to a public variable
SetADOcn cnTest 'passes control to the subroutine that refreshes ADO connections
cnTest.Close

Close #1

Exit Sub

'open configuration program in case of errors
OpenConfig:

On Error Resume Next
MsgBox "The program configuracion values are corrupt or have been deleted.", vbCritical
Close #1
RetVal = Shell(App.Path & "\SysConfigure.exe", vbNormalFocus)
End

Exit Sub

Error_INI:
MsgBox ErrFunction(Err.Description, Err.Number, Err.Source, Me.Name, Screen.ActiveControl.Name), vbCritical
End

End Sub

Public Sub SetADOcn(myCN As adodb.Connection)

'set a new ADO connection
Set myCN = New adodb.Connection
With myCN
.ConnectionString = cnADO
.CursorLocation = adUseClient
.Open
End With

End Sub


basically, that just makes a temporary connection and attempts to activate it. If it connects succesfully, the database is available. If it does not connect succesfully then the database is not available.