Question about Oracle ODBC Connections and VBA 1

[ineedhelplease]

I am working with ODBC connections for the first time and this is why I need help with a couple items.

I am creating a few UDFs that will need to query our production Oracle tables to formulate results. I can use the following code to establish a connection to Oracle.

'------------------------------------------------------
Dim sConn As String, sSQL As String, sServer As String Dim rst As ADODB.Recordset, cnn As ADODB.Connection

Set cnn = New ADODB.Connection
sServer = "SSSSSS"

cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=" & sServer & ";" & _
"Uid= UUUUUUU;" & _
"Pwd= PPPPPPP"

'-----------------------------------------------------

Do I need to establish a new connection each time a function is used? Or can I establish a connection when Excel is opened and continue to use the open connection until the user closes Excel?

If I am able to establish a connection and reuse it again and again, how do I make sure the connection is closed.

Currently I open and close an Oracle ODBC connection each time a function is used. Just as the =sum() function can be used hundreds of times on an Excel worksheet, my custom functions can also be used hundreds of times. I am concerned with the overhead needed to open and close hundreds of ODBC connections on any given worksheet.

If anyone can provide advise or a better method please advise.

Thank you.

 

[SkipVought]

Hi,

"Do I need to establish a new connection each time a function is used? Or can I establish a connection when Excel is opened and continue to use the open connection until the user closes Excel?"

You can declare the connection object as a public variable and open the connection in the Workbook_Open event.

"If I am able to establish a connection and reuse it again and again, how do I make sure the connection is closed."

Then close the connection in the Workbook_Close event.


I've used both techniques. Generally I recommend using the self-contained db access function for limited ad-hoc circumstances. However, for applications that run repeatedly, it is more efficient to open/close the connection once.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}