Persistent ODBC connection in MS Access?

[Zwoker]

Hi All,

I'm using MS Access 2003. I have a form that allows users to choose various selections from combo boxes. When they click the "go" button it runs VBA code that initially builds SQL code to go get the master records from an Oracle database, using an ODBC connection. Using the recordset created from this master query, it steps through each master record and retrieves various transaction records, also using SQL built on the fly for the relevant combination of keys. The final result is displayed in a report.

When only a few master records are found using the user selections the result is very fast - only a few seconds. When a much wider selection criteria is chosen it is very slow - many minutes. I tracked down where the time is being used, and found that it appears to be in the transaction SQL processing. It looks like there is a sizable overhead for each ODBC call that is made.

Is it possible to make MS Access keep some sort of persistent connection, to avoid the overhead that is presumably being used each time for checking security for logging in etc...?

Or is this outside of the control of MS Access, and lies in the ODBC configuration?


Thanks.

[MMcCarthy]

You could try linking the relevant oracle tables and storing the query in Access.

Check out this tutorial

http://www.thescripts.com/forum/thread656528.html