testing for an Oracle database connection in VBA

I'd like to create a function that tests if a connection an Oracle database exists.  I have the following script and I just want to confirm that this is the best way to be doing this....and also, as I am opening a connection and testing that way....is this the best way to do this and should I be closing the connection at the end of the test? Please let me know what the "fastest" way to do this is....and also please provide suggestions for a more standard way to write this.  

thanks

Function db_conn()
On Error GoTo errh
    Set conn = Nothing
    conn.ConnectionString = "Provider=MSDAORA.1;Password=toddo;User ID=cpmdb;Data Source=spinner.sjtc.whirlpool.com;Persist Security Info=True"
    conn.Open
    db_conn = True
    conn.Close
If 1 = 2 Then
errh:
' MsgBox Err.description
 db_conn = False
End If

ohmetwAsked:
Who is Participating?
 
WolfgangKoenigConnect With a Mentor Commented:
yes you should close the connection when the function "Connection" returns true, in my function the connection object "Cn" is definded outside the function in the module context:
Dim Cn As ADODB.Connection

then you only want a check and don' t need the connection then i would close the connection in der function:
Connection = True
Cn.Close
Exit Function
0
 
WolfgangKoenigCommented:
i have coded in this way:
Private Function Connection() As Boolean
On Error GoTo Error:
Cn.Open "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=" & strTNSName & "uid=USER_32;pwd=USER;"
Connection = True
Exit Function

Error:
' process failures
If InStr(1, Err.Description, "ORA-12154") > 0 Then
 Call MsgBox("Fehler ....", vbExclamation, "Fehler:")
end IF
If Err.Number = -2147467259 Then
  ' do things for instance use an another driver
End If

Connection = False
End Function
0
 
ohmetwAuthor Commented:
also, it's quick if there is a database connection, but takes about 5 seconds if there is not a connection....is there a way that would be faster for when in "offline" mode?  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.