Can you clear or reset all cached implicit database connections in

[J Toth]

I have an Access database that queries data from an associated Oracle
database, through linked tables, over an ODBC connection, with code such as:

mySQL = "SELECT RECIPE_STATUS FROM RECIPE"
DoCmd.RunSQL mySQL

Under normal circumstances this works just fine, but I'm finding that there
is a network issue somewhere that is periodically breaking the connection to
the Oracle database, leading to the ubiquitous ora-03113, end-of-file on
communications channel, error. I can trap for the error, but any further
attempt to run a query against the Oracle database results in the ora-03114,
not connected to ORACLE, error. The only recourse is to close the Access
database and start over again.

This has been the only means of recovery I've found with any application
accessing an Oracle database that's hit with the 3113 error, shut it down and
start over again; whether the app is written in VB, VBA, C++, etc.

It occurred to me, however, that if I could clear all knowledge of any
remote database connections within the Access application, that I could rerun
the query successfully, but I can't find any way to tell Access to clear all
database connection information.

Is this even possible?

If so, what would the VBA code be to pull this off?

Thanks,

J Toth

 

[Alex Dybenko]

Hi,
not sure that you can solve this with some coding, you can either reopen
access database, or ask oracle admin to fix connection problem

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[AccessVandal via AccessMonster.com]

Hi,

Since you can trap the error, have you used the connection string to
reconnect to the server?

Most likely a WAN connection? Quality of connection depends on the type of
services you have purchase/choosen from your ISP.

 

[J Toth]

THat is what I'd like to try. I guess I have two questions:
1) Where do I get the connection string information for a linked table (I
assume it's a property of a table object)?
2) Since this was an implicit cursor, i.e., I didn't open an rdo, dao, or
ado connection myself, how would I force the reconnect to the database, with
a dao.databaseopen() call?

I'm prepared to rewrite the code to use explicit cursors, if that's the best
approach.

Thanks

 

[AccessVandal via AccessMonster.com]

Hi,

Here is the connection strings samples.

OLE DB Provider for Oracle (from Oracle)
For Standard Security
-------------------------------------------------------------------------
oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"

For a Trusted Connection
-------------------------------------------------------------------------
oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=/;" & _
"Password="
' Or
oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"OSAuthent=1"
-------------------------------------------------------------------------

In the error trapping code, you can create a Sub or Function in the Error
Code to do the reconnection.
And use DAO or ADO to open the link table. Here's a ADO sample, you can refer
to Help.

Dim oConn As ADODB.Connection
Dim rst as As ADODB.Recordset

Set oConn = CurrentProject.Connect
Set rst = New ADODB.Recordset

oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword"

rst.Open "YourLinkTableNameHereOrSQLSyntax", oConn, adOpenDynamic,
adLockOptimistic

Hope it works for you.