[AccessD] Unstable Oracle Connection

Benson, William (GE Global Research, consultant) Benson at ge.com
Wed Sep 12 13:07:13 CDT 2012


ADO question... I *thought* I was testing enough when I test that the connection string is of a certain form, and that the connection state = 1.

Somehow those tests are still passed for my global connection object but I cannot open a recordset.

This situation happens when I get dropped from the VPN.

Once I log in again, and re-set my global connection variable, things are cool.

However, I am left wondering what are the right tests to ensure I don't wrongly conclude I have a valid connection? Can someone advise what to do in the OpenConnection function below? Don't get too hung up in my inefficient means of testing whether the databaseprovider is a correct string, I am more concerned with discovering a test which will FAIL when I am not going to be able to open a recordset with this connection.


If Not Openconnection Then
    MsgBox "Cannot create a connection to " & [databaseprovider] & "."
    Exit Sub
Else
   Set oRS = New ADODB.Recordset
   oRS.Open SQL, g_Conn, adOpenDynamic, adLockOptimistic
End If 

Option Explicit
Public g_Conn As ADODB.Connection

Function Openconnection() As Boolean
Dim strProvider As String
Dim strTest As String
Dim strDS As String
Dim strUser As String
Dim strConnection As String
Dim strTrunc As String
Dim i As Long
Dim bConnectionParsable As Boolean
Dim S As String    'Databaseprovider
Dim U As String    'UserID
 S = [databaseprovider]
 U = [UserId]
TryIt:
If Not g_Conn Is Nothing Then
    strConnection = g_Conn.ConnectionString
    If strConnection <> "" Then

'''''*******************************************************************
'I DECIDED TO MAKE SURE THE PROVIDER COULD BE PARSED FROM CONNECTIONSTRING
'g_conn.connectionstring will have a value like Provider=MSDAORA.1;User ID=solarconnect;Data Source=CDSPRD10;
'DatabaseProvider is a named range with value like Provider=msdaora;Data Source=CDSPRD10
'''''*******************************************************************
        If Right(strConnection, 1) = ";" Then
            strConnection = Left(strConnection, Len(strConnection) - 1)
        End If
        strDS = UCase(Right(strConnection, Len(strConnection) - InStrRev(strConnection, ";")))
        strTrunc = Left(strConnection, Len(strConnection) - Len(strDS) - 1)
        strUser = UCase(Mid(strTrunc, InStrRev(strTrunc, ";") + 1))
        If UCase(Mid(S, InStrRev(S, ";") + 1)) = strDS And _
            strUser = "USER ID=" & UCase(U) Then
            bConnectionParsable = True
        End If
    End If
End If

'''''*******************************************************************
If bConnectionParsable Then
    If g_Conn.State <> 0 Then
         Openconnection = True
    End If
Else
    i = i + 1
    Set g_Conn = Nothing
    Set g_Conn = New ADODB.Connection
    changeDatabase
    If i < 3 Then
        GoTo TryIt
    End If
End If
    

End Function




More information about the AccessD mailing list