testing for an Oracle database connection in VBA

Posted on 2007-07-31
Last Modified: 2013-12-19
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 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.  


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

Question by:ohmetw
    LVL 4

    Expert Comment

    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

    ' 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
    LVL 4

    Accepted Solution

    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
    Exit Function

    Author Comment

    also, it's quick if there is a database connection, but takes about 5 seconds if there is not a there a way that would be faster for when in "offline" mode?  

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now