Linked Tables performance issues: is a pass through query the only option?

[Err]

Hi,

I've inherited an Access app that allows users to run a custom query using a form. It

-has more than one linked table from SQL Server
-Runs Access Sql on the tables before presenting

The problem is that the tables were relatively small when they were made by my predecessors. Now they are over a million rows each.

It appears that (and please correct me if I'm wrong) Access downloads the entire linked tables before running code on them.

The problem is that our network people are complaining that it is taking up too much bandwidth when it runs.


I've managed to combine a lot of the work in Access to a View in SQL Server and then pare it down with Access SQL. This fixes some of the issues as I'm not downloading unnecessary data and simplifying the Access SQL. I'm worried that it is not optimal.

Is the only solution to build a custom pass through query, run it remotely on SQL Server and then present the data to the users?

Thanks

 

[xenou]

Is the only solution to build a custom pass through query, run it remotely on SQL Server and then present the data to the users?

That wouldn't be the only solution but it would be a good one!

 

[Err]

That wouldn't be the only solution but it would be a good one!

Thank you.

What other options would work that would minimize the impact on the local Ethernet?

 

[xenou]

Having the processing done on the server is always good for reducing network traffic. Other options would probably involve paring down your requests to use even less data than you already are using - pretend like you are still limited to 64K memory.