Advice on Access reporting from Oracle via VBA

[cjashwell]

Hello. I have written a reporting application in Access 2003 that connects to Oracle via ODBC in order to pull in data that is then displayed to users as Access reports.

I have used mainly DAO to connect to Oracle, although I am starting to re-write everything in ADO (sorry, I'm a bit behind the times!).

My issue is, for each of the queries/reports I have set up, the connection to Oracle is initiated once or twice within the procedures that run. I would like to be able to create a connection to Oracle when the user starts the application, that remains open for the life of the program, and therefore avoid having mutiple connections opening and closing frequently.

Can anyone give me any general pointers on how to go about doing this? I have searched the forums here, but haven't found anything about this, but if anyone can point me in the direction of relevant threads that would be great.

Thanks.

 

[Ed2020]

Assuming all of your connections are using the same Oracle data source and login credentials I'd simply declare a global ADODB.Connection object and open it during the system's startup procedure which you're presumably running from behind a form...

Ed Metcalfe.

Please do not feed the trolls.....

 

[cjashwell]

Ed - thanks very much for that.

I have created a module containing the public declaration of the connection object, and the connection procedure with username and password passed from the connect form:

Option Explicit
Public cnImp As New ADODB.Connection



Public Sub OpenConn(strUser As String, strPassword As String)

With cnImp

.CursorLocation = adUseClient

.ConnectionString = "PROVIDER=MSDASQL;" & _
             "DRIVER={Microsoft ODBC for Oracle};" & _
             "SERVER=IMPTEST;" & _
             "UID=" & strUser & ";PWD=" & strPassword & ";"
.Open

End With

End Sub

This works well so far. I can use the cnImp object in other procedures to return data from Oracle.

Am I right in thinking the connection will be closed when the user closes the application, or should it be explicitly closed?

 

[lameid]

You SHOULD always close connections and set objects to nothing when you are done with them. If you have any doubts about this, just remember that Microsoft wrote the compiler.

I suspect that the client side of the connection would (should) die with Access but it may be an orphaned connection on the Oracle side. I know almost nothing about Oracle or I might be able to give a better answer.

 

[jedraw]

You may also wish to look at pass-through query.

 

[cjashwell]

Thanks for your comments, lameid. I will make sure the connection is closed and the cn object is set to nothing.

jedraw - I have looked at pass-through queries, but I find they run much more slowly than ADO recordsets returning data, even when that data is then appended to temporary Access tables.

 

[JerryKlmns]

More tips

faq222-6008

http://www.connectionstrings.com/?carrier=oracle