What is a good way to reconnect to a database? 1

[bigracefan]

I've got an application that runs 24/7. Everything works fine except that on Sundays we do a backup of the database. So the application looses it's connection to the database. So far we need to restart the application after the database is available. I've got the following routine, but it doesn't seem to work. I get the following error message: Operation is not allowed when the object is open.

Here is what I have:
error traping in subs and functions

If Err.number = 3704 Or InStr(1, Err.Description, "ORA") > 1 Then
While Reconnect = False
With frmMessage
.Message = "Trying to reconnect to database"
.Show 1
End With
Wend
Call Form_Load
Exit Function
End If


Public Function Reconnect() As Boolean
'sets the connection locations for security and the ECU tables
Dim Constr As String

On Error GoTo Reconnect_Trap

ReTry:

Constr = "Provider=MSDAORA.1;Password=MyPassword;User ID=MyUserName;Data Source=" & gServer & ";Persist Security Info = True"

gConnect.Open (Constr)

Exit Function

Reconnect_Trap:

If Err.number <> 0 Then
If Err.number = 3705 Then 'already connected
Reconnect = True
Else
Reconnect = False
End If
Else
Reconnect = True 'not connected
End If


End Function

 

[Swi]

I do not see where you are closing the connection. If you open the connection and try opening again you will get that error.

Swi

 

[TomKane]

I found one time (with ado) that the error return codes changed - I can't remember whether it was because of the version of MDAC or the underlying database - you might want to check that 3705 is the correct return code and that there aren't any other eligible ones that could be returned.

 

[Swi]

It doesn't seem as my first post has any bearing. You could try something like this:

Private Sub Command1_Click()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

'========================================================
' Connects to Access 2000 database
'========================================================
On Error GoTo errhandler
Continue:
Do While conn.State = adStateClosed
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\UsersDB.mdb"
conn.Open
Loop
MsgBox "Connected!", vbInformation
conn.Close
Set conn = Nothing
Exit Sub
errhandler:
'If conn.State = adStateOpen Then conn.Close
MsgBox Err.Number & vbNewLine & Err.Description, vbCritical
Resume Continue
End Sub

Swi

 

[bjd4jc]

When you lose your connection to the database because of the backup, ADO does NOT recognize that you've lost the connection. They only way to get an accurate test on the connection is to do a query upon it and then check for errors. If you get an error doing a query then you will will need to

1. Close the Connection

If Not adoConn Is Nothing Then
    if adoConn.State = adStateOpen Then
        adoConn.Close
    End If
    Set adoConn = Nothing
End If

2. Reopen the connection
Since you know for sure that it is physically closed you can just do

adoConn.Open MyConnectionString