I have been working on upgrading an Access 97 database to Access 2007. This database uses tables attached to an Oracle 8i database. I have noticed something that I don't know how to fix. There is a local (production) version of the Oracle database on a server in my location. There is also a test version of the same database located in our corporate office which is connected with a T1 point - to -point (very Slow) connection. When the 97 database does a table join, it passes the join over to the server, where it is executed and the resulting dataset is returned. I have noticed that the Access 2007 verion of the database does not send a join query to the Oracle database, but requests all of the records so IT can perform the join. The data transmitted from the Oracle database to Access 97 database is only the result of the join, about 30 records. The data transmtted from the the Oracle database to the Access 2007 database, since one of the tables contains about 4 million records, is the entire contents of the 4 million record table, which takes about 30 minutes.
This is not a big deal when running from the local (production) Oracle DB, but when testing, I use the test server at the other end of a very slow pipe.
My question is: What is causing 97 to work like I would expect and 2007 to behave so differently? How can I get 2007 to build a join and send it to the Oracle database, like 97 does?
Thanks for any help that you might provide!
This is not a big deal when running from the local (production) Oracle DB, but when testing, I use the test server at the other end of a very slow pipe.
My question is: What is causing 97 to work like I would expect and 2007 to behave so differently? How can I get 2007 to build a join and send it to the Oracle database, like 97 does?
Thanks for any help that you might provide!