Access DB network performance very slow

jwandmrsquared
jwandmrsquared used Ask the Experts™
on
The database I've been asking all the questions about is now ready to be deployed.  Unfortunately, the share drive method to allow the 10-12 users to get to it has a wrinkle.  We have split offices - one in the center of the country and one in the east.  If running on a file server in center, east runs STUPID slow; reverse happens if placed on server in the East.

We are using Microsoft Access 2007. I have confirmed that we have no firewall between the 2 offices and a humongous pipeline only about 30% consumed.  Running a sniffer trace while one east user and one central user produced these 3 errors on the user in the OTHER office from server location:

NT Status: STATUS_OBJECT_NAME_NOT_FOUND (0xc0000034)
NT Status: STATUS_MORE_PROCESSING_REQUIRED (0xc0000016)
NT Status: STATUS_NO_SUCH_DEVICE (0xc000000e)

The users in the location where the server resides have little to no lag and no "bomb-out" problems.

Any tactics anyone would like to suggest?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Access was never intended to be run across a WAN-type setup (which is what you're working with), and you'll find no end of troubles if you insist on making it try to do so. Access 2007 uses the ACE database engine, which is a "file server" type of database. That sort of db has no builtin processing engine, so ALL work must be performed by the workstation. This means that every time you open a form, or a report, Access must pull ALL those tables across the (very-very-very slow) connection, parse them, and then present your data. On average, a typical WAN connection is about 100 times slower than a standard office network; when dealing with internet hops, you can bet that speed is faaaaar worse than that. Access cannot survive in that scenario, and you'll quickly find yourself dealing with corrupt and/or lost data, corrupt forms, etc etc.

You have some options:

1) Move to a Terminal Server or Citrix setup. This can be expensive to setup and maintain, but will provide you with very robust and reliable usage for your off-site users.

2) Use a web-based service like www.eqldata.com. I've never used them, and don't know of anyone who has, so please don't take this as any sort of recommendation. I'm just throwing it out there ;). Services like this cost you up front, and in monthly fees.

3) Rewrite the app to use a web-based interface (i.e. ASP.NET, Perl, PHP, etc etc). This is a good solution, but it will obviously require you to be knowledgable in those languages, and will obviously set back your deployment date.

4) Move your data store to SQL Server, MySQL, Oracle, etc etc and rewrite your FE to take advantage of that platform. This can be a massive undertaking, since in most cases you would need to do a ground-up rewrite - and it you're gonna do that, and you must support remote users, then you would be wise to consider #3.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<< 2 offices and a humongous pipeline only about 30% consumed.  >>
  The problem is, even a "humongous pipeline" over the Internet or even on a point to point is pretty small when compared to the speed of a LAN.
  As LSM has said, running over a WAN is really not an option, but I know that some people do do it (actually, Database MX is the *only* person I know that has done it with any measure of sucess - I'm still amazed he hasn't seen corruption running over a WAN).
  Until you can attack this properly, you might consider splitting the DB (if it's not already split) into a front end / backend and then let the front end maintain an open connection to the back end.  
  The other thing is the use of un-bound forms, but the way most Access DBs are developed, this would mean a total re-write.
JimD.

Author

Commented:
We have a pretty solid citrix environment which is up to speed with the version of Office I used.  We are exploring that option.  Using unbound forms would mean almost a complete re-write...so let's see how Citrix fares.  Lesson learned would be asking different questions next time....I didn't realize there is a difference in speed from LAN to WAN that would mean that kind of constraint (understand that I am an Office power user...not an IT professsional).
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Citrix would be the best choice. You can generally do this with no code/structure changes (except you'd probably want to implement the split configuration if you haven't already done so).

Author

Commented:
Why would I want to split the front/back end if I go Citrix? It would mean I would need to provide the front-end to about 25 users, and if I change something...provide it again.  What additional benefit comes from splitting it in Citrix?
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
There are a multitude of reasons to split the database (performance, less chance of corruption, less chance of record locking, etc etc etc) but only ONE reason to not split it - to make updates easier on the developer.

In a Citrix environment even that won't wash, since it's quite easy to setup a batch file to run everytime the user logs into Citrix, and have that batch file copy over a new, fresh copy of the FE. When the user launches their FE, it will be the latest version. Assuming you "relink" the "master" copy of the FE to when you deploy it, your users will never know that they've been given a new FE.

The batch file can be as simple as this:

@EchoOff
copy "source", "destination"

Where "source" is a pre-defined location on the Citrix box, and "destination" is the location on that users' desktop where the file is to be replaced.

I use this in the Terminal Server environments that I work with, and it works flawlessly.

Author

Commented:
As always LSM, you are a wealth of knowledge.  Initial tests of Citrix show it to be a great solution.  Now to create another question regarding Outlook object and Citrix.

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