Comparing records in an Access table to multiple tables in Oracle DB

[ejm8]

Hello,

I need some help in finding the best way to compare records in a Access table to the records in 6 different tables (with millions of records each) in an Oracle DB via ODBC, using VBA...

Notes about the tables in the Oracle DB: (1) It's not an option to modify the them, (2) Access doesn't see any of their fields as being indexed (I don't know if they are or not), (3) the tables are all layed out the same, and for my purposes I would prefer to see them all as one table.

What I want to do is walk thru my Access table and try to find records matching critera in one of the 6 Oracle tables, and if I find it, compare another field in the two tables, and update the record in my Access table with the result of the comparisions.

Right now I have some very slow code that opens each of the 6 Oracle tables as ADO recordsets, one at a time, and walks thru my Access table (also opened as an ADO recordset) and uses the .Find command to try and locate the record in each table.

Here's an example of the tables:

Local Access table (Access_Table):

ACCT_NUM   EMPLOYEE  VERIFIED
1          56001     N
2          56002     N

The 6 Oracle tables (Oracle_Table_1, Oracle_Table_2, etc):

ACCT_NUM   EMPLOYEE  MANY OTHER FIELDS...
1          56001     ... ...
5          56008     ... ...
9          56001     ... ...

In the example above, I simply want to see if account 1 from Access_Table exists in Oracle_Table_1 (or 2 - 6), and if it does, I want to change the VERIFIED field in Access_Table to "Y".

What's the best way (speed wise) to do this? Do the .Find directly on tables? Import all six of the tables into a temporary table in my Access DB, then do the .Find on that table? Other ideas?

Thank you!

 

[PHV]

Create a saved union query named, say, qry6oracle:
SELECT ACCT_NUM, EMPLOYEE FROM Oracle_Table_1
UNION SELECT ACCT_NUM, EMPLOYEE FROM Oracle_Table_2
...
SELECT ACCT_NUM, EMPLOYEE FROM Oracle_Table_6;

And now the Update query:
UPDATE Access_Table AS A INNER JOIN qry6oracle AS O ON A.ACCT_NUM = O.ACCT_NUM
SET A.VERIFIED='Y';

You may perhaps add the EMPLOYEE field in the JOIN clause to be sure ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ejm8]

Thanks for your reply PHV!

That's quite a bit faster than the code I was previously using, however it's still slow (the union query takes about 3.5 minutes).

Right now I'm linking the tables from 6 Oracle databases, and running the SQL off the them. I've read that opening the tables directly (ADO recordsets from the DSNs in VBA) would be faster, however I don't think I could acomplish the union query this way since the tables are in 6 seperate databases?

Any other ideas for speeding this up?

Thank you!