How to 'grab' the ODBC connection used by linked tables

[DanR]

Hello Accessers

I am looking for your advice on how to set an ADO connection object to use the very same ODBC - Oracle connection that Access 2000 opens for tables that are linked to an Oracle database.

Rather than opening a new connection - eg cnnOracle.Open etc, I would like to access the same connection used by the linked tables.

Can anyone suggest the VBA code to do this??

I'll be eternally greatful

Dan

----------------------

To explain what I mean, and why this is needed:

The database in question has an Access FE, Oracle BE. The FE contains ODBC linked tables pointing to the actual tables in Oracle.

Several of the Oracle tables are temporary tables, which means that they are created in Oracle each time a user connects, and they are unique to that connection. This means that two users logged on simultaneously could have different contents in these tables. I have done this at the request of the Oracle DBA, but it fits well with the program logic.

Using these tables through their linked table in the front-end works fine. But I also need to use a direct ADO connection to the Oracle back-end to run stored procedures.

But when an Oracle connection is created, such as:

Set cnOracle = New Connection
strConnect = "Provider=MSDASQL;DSN=DAHD;UID=" & strUser & ";DBQ=DB_DEV;pwd=" & strPassword .....
cnOracle.ConnectionString = strConnect
cnOracle.Open​

it creates a separate connection to Oracle, and hence separate instances of the temporary tables which don't reflect any changes made via the linked tables visible in the front-end.

While it may be possible to find another workaround, all my problems would be solved if I could just set my ADO connection object cnOracle, to point to the same connection that Access makes in the background for the linked tables.

 

[WayneRyan]

Hi Dan,

The link below, from Dev Ashish, shows how you can programmatically
link tables. I'd guess you could make the connection global and use
it in your Access Front End.

http://www.mvps.org/access/tables/tbl0010.htm

hth,
Wayne

 

[DanR]

Thanks, but that's not quite the problem...

Thanks Wayne

That piece of code will come in handy, but if I understood it right, it doesn't deal with my current problem. I have no problem creating ODBC linked tables to work with Oracle.

The issue is that when I use these linked tables, Access automatically creates a connection to Oracle, and this connection appears to remain open so long as the database is open.

When I use VBA to create an ADO connection to Oracle it is always a new, additional connection.

My problem is how to point to the original connection, so that both the linked tables and ADO connection object are using just the one connection - I hope this makes sense.

Dan

 

[WayneRyan]

Dan,

It makes sense, but if you establish the connection and link the tables
when your db opens, you'll know the connection and can use it in your
database.

Will research and let you know if I find anything.

Wayne

 

[DanR]

... if you establish the connection and link the tables when your db opens, you'll know the connection and can use it in your database.

How would I get the connection object using this code? Getting the connection string is no problem, but not clear how I would extract a reference to the actual connection object from this code

(Also this code raises the NO DSN error and shows the message: The User DSN for Oracle Tables were not found. Please check ODBC32 under Control Panel even though the current OBDC links work.)

Perhaps there's a different approach...