access performance over a network

aeolianje
aeolianje used Ask the Experts™
on
I have several MS Access 2002 applications (soon to be moved into 2007) that either have a front-end on the user's machine and link to data on a network -- or run directly off the network.  These apps run quickly in the office -- but are terrible when working remotely.  The network speeds are faster in the office then remotely.

Is there anything that can be done to improve performance for running these applications remotely?

Thanks for your help,
je
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Whats your link speed? . You shall need to upgrade the WAN speed and also make sure the load of your Firewall and other perimeter components.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
 You need to understand that JET was not designed to run over a WAN and it’s not recommended to do so.  Can you do it?  Yes, but to do it successfully you'll need to have a high end network.

  For decent performance, you need a connection that approaches a speed of 40mb/sec speed or better with pings down in the 10ms or less range.

  So where does that leave us?

 With the current app using JET, the only thing you can do is ensure that indexing is proper and that compact and repair is being done on a regular basis.   If after that, performance is not acceptable, then either the network needs to change, you need to some type of remote access solution, or the app needs to change.

  I'll tackle those one at a time.  

NETWORK:

   On the network, you're only going to get it to work once you get past low end LAN speeds, which is 10mb/sec.  And I doubt you'd see what most consider "acceptable" performance until you get into the 40mb/sec range.  That is equivalent to a T3 connection or better.

  I think that when you investigate the costs associated with that, you'll be moving onto doing something else.

REMOTE ACCESS:
 One of the popular choices is running a Terminal Services server in the main office, then have remote users connect to that.  What this does is allow the app to run on the local LAN and the only thing going over the network is KVM (Keyboard, Video, and Mouse) data for remote users.   So this is ideal for low band width situations.  It's mostly a "plug and play" type situation, with little or no modifications required to the app to get it to work.

  The other option is to use a service like: www.eqldata.com, which allows remote users to access the app through a web browser.  Again, only KVM is going across the internet, so the app is fast.

  Some also go the route of using Citrix on top of terminal services, which achieves the same thing basically as using the service from eqldata (running the app through a web browser).  It's not quite the same as what eqldata is doing, but from a user perspective it amounts to the the same thing.

  Overall, if you don't want to change the app, a remote access solution would be best.

  There is also Sharepoint 2010 and Access 2010, which allows you to share a DB across the net.  Problem is that it's pricy (you need an enterprise license) and it's limited (a "web" enabled DB is not allowed to use VBA for example).

CHANGING THE APP:

   First thing would be to ditch JET as the data store and use SQL Server or something else in its place.  At a minimum, that's fairly easy.  Move the data to SQL server and then create linked tables in Access via ODBC.  Your app pretty much works as before, but now some stuff is getting done server side.   You may or may not find performance acceptable at this point.

  Going a little further with that, you can start to use views, stored procedures, and triggers.  The use of all these starts pushing processing server side rather then have it happening on the client side, which is where JET does all its processing (with JET, the server is nothing more then a file sharing device).

  Last is re-writing some of the app to do more typical client/server type things; only fetching one record at a time to work with, setting up searches to return sub-sets of data rather then entire tables, etc.  Most don't go for this approach as in most cases, it's a total re-write and at that point, one must consider if there is a better alternative to Access to do the development with.

Jim.

Author

Commented:
This was very thorough and extremely helpful.

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial