How to speed up off-site access to Access application

Your general application architechure (Access with Linked Tables) is probably flawed for this purpose. It can probably be fixed (mostly) but not without some revision.

The key issue is, that when Access is the front end, Access itself determines whether it can submit the entire query for processing by SQLServer, and often comes to the conclusion that it cannot, so it often performs the query itself, by downloading the whole table. This is particularly true with Joins, especially if there is not a strong primary key on each of the tables in use.

Here is the MS Technet doc on performance for Access Linked Tables with SQLServer backend: https://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx

The most pertinent bit is:

The major cause of query performance degradation is when a query involving very large tables requires that all of the data from one or more tables be downloaded to the client. This can happen even when joins or criteria appear to limit the result set to a small number of records. This occurs because sometimes the Office Access database engine determines that it cannot submit an entire query to SQL Server. Instead, it submits multiple queries, often including queries that request all of the rows in a table, and then it combines or filters the data on the client. If the criteria require local processing, even queries that should return only selected rows from a single table can require that all the rows in the table be returned.

Using the JET/ODBC diagnostics utilities to determine where all the work is being done.
Using Snapshot RecordSets in access where possible.
Push query logic to the SQLServer with Views and Stored Procedures/Functions.
Establish solid Keys and Indexes in SQLServer, and make sure the indexes are defragmented as needed.
Caching commonly used data in Access.

The link above has lots of great advice and technical details for someone in your position. Good luck!

Frank Thomas

Posted 2016-04-14T15:39:38.883

Reputation: 29 039

Thanks for the link. I'm sure it will be helpful regardless, but it sounds like the advice is not specific to a slowdown through a VPN. My application speed on-site is just fine, the problem is just when accessing it through a VPN. If I maximized the speed using the tips in the article, it would be faster but likely not fast enough to mitigate the slowdown through the VPN. My question is more wondering if there was a way to make the application work well off-site, whether it be increasing the capacity of the VPN, making the application internet-facing (if that's even an option), etc. – Mike – 2016-04-14T17:56:55.357

VPN throughput is much higher latency than a LAN connection. I once wrote a .Net app to work over a VPN that used DataSets as its data access technology, and everything worked perfectly in our office, because 12MB transfers in about a second on a 100mbps link, but took our vpn connected DSL using folks 5+ minutes to transfer over the wire. You app runs acceptably on a low latency link, but to address the concerns of a high latency link like a remote VPN connection, you will have to do what I did; refine your data access methodology to pass only the absolute minimal data required for the task. – Frank Thomas – 2016-04-14T18:38:32.380

About the only think you could do, without revising any of the existing structures, is to provide the application through RDP/Terminal Services. Have your users log into a remote desktop over the VPN, and run the access app from there. That will keep all your data within your network, and will only serve to the remote users an visual rendering of the data in the form of screen frames, so that should dramatically reduce the amount of data passed over the wire. It will probably cost a small fortune in licensing if you need concurrent multi-user access, but its prolly easier. – Frank Thomas – 2016-04-14T19:50:27.980

How to speed up off-site access to Access application

Answers