Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

testing for an Oracle database connection in VBA

Posted on 2007-07-31
3
Medium Priority
?
1,551 Views
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 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

0
Comment
Question by:ohmetw
  • 2
3 Comments
 
LVL 4

Expert Comment

by:WolfgangKoenig
ID: 19599393
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
 
LVL 4

Accepted Solution

by:
WolfgangKoenig earned 2000 total points
ID: 19599424
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
 

Author Comment

by:ohmetw
ID: 19602362
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses

810 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