Include Oracle tables and local tables in ADO query

[Tranman]

Hi all,
I need to know is there any way to create a select query in a module (ADO, oleDB) that includes columns from both local Access tables and remote Oracle tables in the same query WITHOUT USING LINKED TABLES/ODBC.

I already know how to do this with linked tables and DAO or ADO, but I want to do this without using ODBC in any way. (We are having difficulties with ODBC since we upgraded Oracle to 8.1.6, and are trying to isolate the problem.)

Thanks in advance for any help.

Paul

 

[cmmrfrds]

I don't work with Oracle, but I have joined SQL Server tables to Access tables. Maybe Oracle has the same features. One, is to use linked tables on the SQL Server side. SQL Server allows linked servers, which can be an Access table. The linked server can be treated like any other table in the join. Another way is with temp tables. How this works is to open up a connection to the server and then write the access table to a temporary table in SQL Server. Then, leave the connection open, and follow up with the sql statement that joins the temporary table to the server table. Important to leave the connection open between calls or the temporary table will go out of scope and disappear. Both of these methods work with SQL Server and will maybe give you some ideas for use on Oracle.