Forum Discussion
Speed of Access across multiple states
I work for a small company that has locations in 3 states. The servers are at my site and I don't notice any speed issue when working with databases onsite. However, the other two sites experience significant speed issues. It doesn't matter what database - simple queries take 10 to 20 seconds when they are almost instantaneous onsite. Some functionality just plain doesn't work.
The issue is definitely specific to MS Access as we are able to have TEAMS meetings, screen sharing, etc with no lag or degradation in performance of other applications.
Front end / back end helps a little, but not enough. Other things that I have found on the web are so insignificant, I really haven't found anything that helps. Does anybody else have a database that works on a network across the country?
This is not an uncommon description of performance on local vs WAN deployments of Access. There are some important things to consider.
First, Access is unlike most other applications because it sends a lot of traffic back and forth between the front end and the back end. However, to be sure what we are discussing, please confirm that this is a split application with interface objects in the "Front End" accdb and the tables in the "Back End" accdb.
A seminal discussion of the problems of WAN performance can be found here.
Even though it was more than 20 years ago, Albert's discussion is entirely relevant today. Although speeds have increased in all areas, the ratios are still the same: WAN connections are many times slower.
How do you deal with it?
First, there are ways to design forms that maximize performance speed, and, unfortunately, there are ways to design forms to minimize performance speed. And the really difficult thing is that the "out of the box" approach to forms design in Access is probably on the slower end when deployed to a WAN.
The basic idea is that a form can be bound to a table, or it can be bound to a parameterized query. That matters because a form bound to an unfiltered table pulls every record from that table across the WAN and loads it in the form. Then, typically, the approach is to apply a filter to the form to focus on one record. Slowest possible design.
The alternative, in which the form is bound to a parameterized query, pulls one, or possibly a handful, of records from the table, not the entire table.
Other design factors that slow performance include forms with multiple subforms and multiple combo boxes or list boxes. Each such connection requires a call across the WAN to pull that recordset. It all adds up.
So, focus on design. Search for discussions of ways to optimize performance over a WAN.
There are other ways to handle the problem, such as Remote Desktop, in which the work is done on the same computer where the accdb resides inside the LAN, and only the screens for display are pulled back and forth across the WAN. Look into deploying your application this way as an alternative.
- Thank you. I can confirm that front end is the queries, forms, reports, etc. and the back end is just tables.
I will have to familiarize myself with parameterized queries. I assume you would place those in the front end?
Regarding the subforms and subreports, Ironically, I have had multiple issues with dlookups in standalone text fields, so I recently switched to using multiple subforms instead. I'll have to investigate yet another option...
ThanksYes, the queries are in the front end.
I don't know that there's much difference between combo and list box heavy forms and forms with subforms in this regard, but it probably is situational as well.
The general term for this design approach is "client-server". The idea is that the client is very precise about requesting specific data from the server.
You should be able to find a lot of relevant discussion in forums and blogs.
