Options for increased performance over VPN

[Sck]

Over the past 10 years I have developed about 15 different Access databases for my company. they are all split databases (linked tables) with the backend on a corporate server and the frontend on local computers. this setup works fine for individuals on the LAN but there are a good number of users that have to use VPN to connect to the backend databases. The issue is that the databases are extremely slow over VPN (get a cup of coffee between updates slow)



I have been asked to look into utilizing SharePoint or SQLserver to see if it would resolve any of the VPN issue. I don't know anything about using Access with SharePoint. My initial thought is that the issue is not the database it is just the VPN speed. Just moving files over the VPN is slow so a different backend setup is not going to make a major difference.

I have gone through a couple of the frontends to try to limit data transfers to only when needed but it did not make any major difference in performance.

Any thoughts on options to help VPN performance? I can get approval to do whatever is needed but I really do not want to consider rewriting all of frontends if posable.

ADDITIONAL INFORMATION: The databases were all developed in Access 2013 (32 bit) and earlier with significant VBA coding. I am in the process of adjusting code to work in the corporate standard of Access 2016 (64bit) - not my call on that one!!

[ranman256]

no much you can do.
VPN sucks a lot of performance.

[June7]

Why would company adopt Access 64bit as standard??? Review https://www.devhut.net/2017/04/13/ac...compatibility/

[Sck]

I have no clue but that is not something I can fight, I just have to live with it. In a large multinational company, decisions like that are well above my paygrade.....

[Micron]

I can get approval to do whatever is needed

I not a betting man so I hesitate to take that one on. I think your solution needs to be terminal services or Citrix. In either case, the actual fe would reside locally and be accessed from elsewhere. I'm not sure if ts fits a "I'm in another city" type of scenario but Citrix would. I've used it from home to access fe on network and found the response to be quite acceptable. That's saying something, given that traffic had to go through my router, then ISP router before getting to the work network. AFAIK, SharePoint and Access is no longer considered to be the best thing since sliced bread if you ask M$oft.

[Sck]

would terminal services over vpn be a viable option? if I understand correctly, using

terminal services means that the actual data is not traveling over the vpn which should solve the issue. What other "issues" should I be concerned with?

Is this correct -
FE will be on a LAN computer with the BE on the server.
this FE would connect to the BE via LAN.
the terminal services would allow remote "VPN" users to access the FE and only the display type data would go over VPN to their remote pc.

Would each remote user have their own instance of the FE if multiple users were on at the same time using terminal services

.

As you can tell, I have never dealt with terminal services …..

[Micron]

My knowledge about ts is limited to that of being a user. You get a monitor, keyboard, mouse and a little black box - no pc. What you interact with is really an image on the monitor. Everything is on the server - apps, files, network & what passes for your C drive. In a multiple server environment, it's possible to lose you shortcuts and such if not set up right as there's no guarantee that you log onto the same server each time. Don't know if there's a need for a vpn in a ts case because I don't understand what's going back and forth between the user and the server. Is it just image data or what? Hope that helps at least a bit with those questions. Citrix wouldn't require a big hardware purchase and configuration that you probably don't have.

[ArviLaanemets]

You have Terminal Server in your LAN;
You have Access installed for your Terminal Server Users;
You have BE on shared resource (database server with folder system available for users according their rights) in your LAN;
You have all users working with your database (directly, or over VPN) registered as domain users. They must have RW rights for folder with BE in it;
For every user working with DB over VPN, you have user profile in Terminal Server;
Every user working with DB over VPN has FE in his/her Terminal Server Profile folders (Desktop or Documents), or on some other network resource accessible for him/her. Every user has his/her own FE, and using FE of another user (when it is accessible too) is frowned upon.

User sets up VPN connection in remote laptop/computer, and logs in. Now his/her computer is linked with LAN;
User logs in into Terminal Server in LAN, and can start with work.
(Depending how the VPN connection is set up, last 2 steps may be merged - Opening VPN connection in remote computer forces connection to Terminal server automatically.)

Users residing in LAN may have their FE's in their computers, but they also can open Terminal Server session, and open DB from there,

[Micron]

Don't they need delete permission on the folder as well? Otherwise they leave lock file behind?

[Minty]

@Micron - Not sure they do - I believe the application can effectively override the permission issues as it write / deletes the file.

Pretty sure I have had Access open a lock file on a read-only folder before. (When some numpty tried to open the network master copy of a Front End even though they only had read permissions....)

[pbaldy]

I have a couple of applications that run pretty well over a VPN (I have SQL Server as a back end). You have to be REALLY careful about what goes back and forth. You can't just open a form bound to a table with a lot of records. I had unbound forms where the user would enter parameters, then I'd go get the single record they were looking for.

The suggestions to use Citrix/TS are certainly more flexible options if feasible for you. Another option is virtual machines that users remote into, if you have an environment that uses them. We just did that for 3 remote users.

[Micron]

Pretty sure I have had Access open a lock file on a read-only folder before

Sure, but that's not what I'm saying. If a user attempts to open db and they left the lock file there, depending on Access settings, they can be prevented from opening it. Can't recall what the factors were that came in to play, but I've seen this happen where users weren't given delete permissions on a folder and somebody couldn't open db because there was a left over lock file.