Query Oracle table and local Access table in one sql statement

[jogunjobi]

Hello,

I have a situation and I really need help with that. I have an access form that I use to query Oracle tables (via ODBC). Right now, I'm trying to run a SQL query against an Oracle table using values from a local Access table. I keep getting an "ORA-00942 error:table or view does not exist" basically telling me that the Local Access cannot be found. See SQL below.

SELECT column1, column2, column3
FROM OracleTable
WHERE column1 IN (SELECT column4 from AccessTable WHERE column5 = 'text')

Any help with the sql syntax will greatly appreciated. Thanks

 

[MSAccessRookie]

Hello,

I have a situation and I really need help with that. I have an access form that I use to query Oracle tables (via ODBC). Right now, I'm trying to run a SQL query against an Oracle table using values from a local Access table. I keep getting an "ORA-00942 error:table or view does not exist" basically telling me that the Local Access cannot be found. See SQL below.

SELECT column1, column2, column3
FROM OracleTable
WHERE column1 IN (SELECT column4 from AccessTable WHERE column5 = 'text')

Any help with the sql syntax will greatly appreciated. Thanks

On the surface, your Syntax looks OK, but if I understand your situation correctly, then the error could have more than one meaning. You are correct in saying that it could mean that the Local Access Table cannot be found, but it could also mean that the Oracle Table cannot be found. This often indicates a spelling error in either Table Name.

Creating two separate Queries that poll only one of the two Tables should determine which Table is the source of the error. Feel free to let us know the results.

Query1:
 
Select Column1, Column2, Column3 From FROM OracleTable;
 
Query2:
 
Select Column4, Column5 from AccessTable;

 

[mdlueck]

Where do you need the selected Oracle records to end up: In an Access table, or in VBA variables?

If an Access table, then I suggest you do the query in a nested DAO.QueryDef configuration where the inner DAO.QueryDef object is configured in Pass-Through mode meaning that Access will not tinker with the Oracle specific SQL. The outer DAO.QueryDef simply selects all of the records from the inner DAO.QueryDef and places them into an Access table.

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

If you would like the results in VBA variables, then I would suggest you use ADO objects - an ADO.Command and ADO.Parameters objects to send the SQL to the Oracle BE DB.

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

You would just need a compatible ADO.Connection object to pass to the ADO.Command object, you may learn about such here:

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259

 

[jogunjobi]

On the surface, your Syntax looks OK, but if I understand your situation correctly, then the error could have more than one meaning. You are correct in saying that it could mean that the Local Access Table cannot be found, but it could also mean that the Oracle Table cannot be found. This often indicates a spelling error in either Table Name.

Creating two separate Queries that poll only one of the two Tables should determine which Table is the source of the error. Feel free to let us know the results.

Query1:
 
Select Column1, Column2, Column3 From FROM OracleTable;
 
Query2:
 
Select Column4, Column5 from AccessTable;

I already queried both tables independently and they both produced results successfully. However, when I try to make the access query as a subquery, I get the error message

 

[mdlueck]

I already queried both tables independently and they both produced results successfully. However, when I try to make the access query as a subquery, I get the error message

I believe that would be due to the fact that one is via the Oracle connection and one is via the local Access connection.

It would be best if you could harvest into VBA variables the data needed from Access, then submit off a Pass-Through query to Oracle.

So basically run the Access query via ADO objects, obtain VBA variables with the result, then submit a Pass-Through query to Oracle including the Access values.

 

[Pat Hartman]

I would create a querydef for the sub query and then join that querydef to the Oracle table. Depending on how many rows the Access side of the query returns, Jet/ACE will either request the selected records one at a time from Oracle or it will ask Oracle to send down the entire table and Jet/ACE will perform the join locally.

Although it is possible to join heterogeneous tables, it is not efficient. If speed or network traffic becomes an issue due to the size of the Oracle table, try appending the selected Access records to a table in the Oracle database so the join can be done on the server and only the results returned.

A pass-through query is not possible with a heterogenous join.