Speed of Access across multiple states

jwardymer 

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.