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

Err

Active Member
Joined
Nov 16, 2006
Messages
274
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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!
 
  • Like
Reactions: Err

Err

Active Member
Joined
Nov 16, 2006
Messages
274
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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.
 
  • Like
Reactions: Err

Forum statistics

Threads
1,082,318
Messages
5,364,522
Members
400,804
Latest member
davileal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top