Table comparison between two Oracle databases using VBA

[codehead]

Hello,

I am trying to compare the same table on both our development and production databases to find the differences. I can connect to both databases with Access 97 and I have linked to the tables in it (they are too large to import), but I can't seem to figure out how to compare each field for differences. I suspect that I need to write some VBA code, but I need an example. Or I could create a small Java program, but again I need an example to use as a starting point. I asked my DBA to link the two tables, but he refuses to do it because of the administration headaches. Has anyone done this before? Can anyone point me to an example/reference?

Thanks!

 

[cmmrfrds]

Oracle must be a pain to work with since it would not be a problem to link the table in SQL Server. If this is not something that is going to be long term, I would ask the DBA to copy the production table over to your test database so that all the processing could be done on 1 server. The DBA should be able to schedule a job to run on a periodic basis.

When the tables are on 1 server, you can either write a stored procedure in Oracle to make the comparisons, or in Access a pass through query or ADO query. I would avoid linking the tables since that will be extremely slow if it works at all with a large volume of data - keep processing on the server. If you need help on one of these options, then explain what the queries need to accomplish. Also, how many records need to be compared.