Linking to Oracle tables

[Elsa]

Hello, this is my first message to tek tips.. Here I go. <br>
<br>
I am using MS Access 97 to report off of Oracle tables (ver 7.3.4) by linking to them. When I am creating queries: If I walk away from my pc for 5 to 10 minutes or do not access the Oracle tables for that length of time, MS Access will GPF on me when I try to access the tables/queries again. I think what is happening is that Oracle is inactivating my connection and MS Access has a problem with this. Is there a fix to this problem (ie: need to change something in Access's configuration?)<br>
<br>
Appreciate any help.<br>
<br>
Thanks

 

[bookor]

What ODBC driver are you using? I use the standard MS Oracle ODBC driver to link Oracle8 tables in Access97 and have never had any problem. I havnt tried any of the new OleDB drivers or other methods of linking, but they might also be an option if you dl the MS MDAC components.<br>
<br>

 

[Jonathan]

As I understood it, the connection will be closed after being idle for some time, but it is cached by access and should be reopened as soon as you try to do anything.<br>
<br>
I am using Oracle 7.3.4 also, and I haven't had any problems with this. The way I do it is as follows:<br>
<br>
When the app starts, the user has to enter their (oracle) login and password. To test this, I then use that data to assemble the oracle connect string, set the connect string of a querydef to that and the sql to something like "select * from dual" and then trap errors that come back - the ODBC Call failed and ODBC Connection Failed errors generally mean that the username/password was incorrect. If the query is successfully opened, then access caches the connection, the connect string gets stored in a global variable.<br>
<br>
The table are attached without storing the password, so when a table is used directly, the cached connection is used. Whenever I use a SQL pass through query, I set the connect string first to the globally stored one I assembled in the logon stage.<br>
<br>
I think this is a pretty standard way to do it. The "Building Applications with Microsoft Access 97" book on the MSDN Online Library has a chapter on developing Client Server apps which is quite useful.<br>
<br>
Hope this is of some help<br>
<br>
Jonathan