Upgrading from 97 to 2007 (1 Viewer)

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!

no idea, technically

I always found A97 very "light" and fast. eg only needed 128Mb. later versions needed more memory etc, as they turned into bloatware.

may be a matter of re-writing the query to get the server to return fewer rows.

Welcome aboard

I assume that the tables being joined are actually in the same database. If they are not, Access will request both tables (applying criteria if possible) and do the join locally.

You must have access to the tracing features of SQL Server to have determined that. You could start by trying different ODBC drivers. I have only SQL server and SQL Server Native Driver 10.0 on this PC. But there are others. Changing the driver means changing the DSN but doesn't require any query or code changes so that is where I would start.

If that doesn't solve the problem, I would try to break the query up and then nest the pieces. Even though the ODBC driver will attempt to compress the nested queries into a single query, sometimes you can convince it to do things your way by showing it the true path. After that, I would switch from DAO to ADO or vice versa if you are running the query in code. Also, you could create views on the server to force the join to happen there.

As long as the view doesn't aggregate the data and as long as all the tables in the query are individually updateable, the view will be updateable provided you supply a pseudo index. When you link to the view, Access will prompt for a unique identifier. Choose the columns carefully and don't miss any. If you don't actually create a unique identifier, updating the tables through the view could corrupt them.

Refreshing the view may cause the pseudo index to be lost. To recreate it at will, create a DDL query to build it in Access just as if the tables were local. Running this query will not physically alter the linked tables but it will just build a pseudo index for Access to use.