'Connection failure' error

[vovan1415]

I am using the following statement to check the connection:

If CnDB.State <> 1 Then
'reconnect
End If

If the connection is open (CnDB.State = 1)
then I try to open the RecordSet based on this connection...

But sometimes I got the error 'Connection failure' even
CnDB.State still equals to 1.

How to check the connection?

 

[Swi]

If conn.State = adStateOpen Then
conn.Close
Else
conn.Open
End If

Swi

 

[Swi]

Sorry, I posted a bit prematurely. I meant to ask if you could post your code as well. This quick example of mine seems to work fine.

Option Explicit
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer


Private Sub Command1_Click()
'========================================================
' Connects to Access database
'========================================================
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\UsersDB.mdb"
conn.Open
If conn.State = adStateOpen Then
rs.Open "SELECT * FROM Test", conn, adOpenKeyset, adLockOptimistic
Else
conn.Open
rs.Open "SELECT * FROM Test", conn, adOpenKeyset, adLockOptimistic
End If
For i = 1 To rs.RecordCount
MsgBox rs.Fields(0).Value
rs.MoveNext
Next
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

Swi

 

[bjd4jc]

If the connection to the database somehow was broken your connection state will still show as open. The only way (that I know) to adequately see if you still actually have a connection or not is to do an actual query against it and check for error codes afterward.

 

[CasperTFG]

What I do is open the conenction at the start. Then i have a routine that gets recordsets for me...

Private Sub GetRecords(sSQL AS String) AS ADODB.Recordset
Dim blRetried AS Boolean
Dim rs AS ADODB.Recordset

blRetried = False

SQLRetry:
If conn.State = adStateOpen Then
Set rs = conn.Execute sSQL
End If
If err.Number <> 0 Then
If Not blRetried Then
Call ReConnect() 'This function re-connects to the DB.
blRetried = True
goto SQLRetry
End If
End If
End Sub

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".