ODBC connection Access and Oracle

[willem mosterdijk]

In a access database I have a connection between MS Access and a Orcale database. Once in a while the connection dies. Does anyone know a vba code that the connection test and possibly re-create the connection as needed.

 

[mdlueck]

I think it would depend on what type of objects you are using with the ODBC connection.

For example, I know that once an ADO.Connection object looses its connection, it must be destroyed and a new one created.

 

[willem mosterdijk]

I think it would depend on what type of objects you are using with the ODBC connection.

For example, I know that once an ADO.Connection object looses its connection, it must be destroyed and a new one created.

Thanks for your answer. I don't now how I can see what type of connection it is. I uses the connection wizard (ODBC) and select the Oracle driver to connect to the database. So far so good. I can work in the Access program, but sometime I receive a connection error. So if there is a VBA code that test the connection and if it failes it will be re-connected I would be very satisfied.

 

[Trevor G]

Willem,

Try these links to see if they help you.

http://www.techonthenet.com/access/questions/oracle_connect.php

http://docs.oracle.com/cd/E12151_01/doc.150/e12154/perf_tuning_access.htm

 

[mdlueck]

I don't now how I can see what type of connection it is.

Not the connection, the type of database object within Access you are using with the ODBC connection.

1) Linked Table = (DAO)
2) ADO Objects = (ADO)
etc...

Then once that is established, find a way to recover from the connection drop that is correct for the object type.