MS Access poor performance based on location

[aernst]

I found some helpful replies to someone's post regarding data base replication. I started approaching my problem pretty much the same way but need someone with way more knowledge than me to provide guidance. Basically I know very little about these things but want to learn so please bear with me.

The symptoms:

• I created a very very small MS Access DB (first one - indicative of current knowledge level).
  It did what it was supposed to do, driven by menus and submenus; with forms for queries and data entry, along with options for outputs in Excel and printable matter (wow was pretty proud until...)
  Performance was perfect, form loads took milliseconds.
  Placed this DB on a shared network resource local to me (same building) still worked just fine.
  Test subject at a distant location found the performance to be horrible

.

Discovery and attempted solutions:

• I had the test subject disable her real time virus checking software - no improvement.
  Measured network latency found mine to be <1ms and theirs to be <35ms. I didn't think this was much to be concerned with but this is the first question...Is it?
  Reason for me asking - I read a white paper with regards to this and MS's behavior when opening and editing Office application across a network where packet sizes are generally 4k making latency an issue; however, the study failed to mention if MS Access was considered part of Office and subject to the same 4k packet size.
  I separated the front end from the back making the table (THAT ONLY HAS 20 RECORDS IN IT) the only item on the shared network resource - problem persists.
  I had test subject copy a full blown version to her laptop and it worked perfectly.
  Had support center folks provide network optimization software to her laptop with no visible improvement

.

As a full blown version works on hers is the solution to provide her/them with a full blown copy and create VBA code that force replicates this DB when opening and maybe closing the DB?
Or, would a better solution be to use the DoCmd.TransferDatabase when opening and closing the DB?

Thanks in advance

[puppydogbuddy]

Have you been able to identify where the slowdown is, e.g. does it occur when forms are loading or when you are processing updates via action queries or recordsets? or could it be hardware related....e.g. is the LAN broadband sufficient?

Before I did anything else, I would check the Performance FAQ in detail at Tony Toew's web site http://www.granite.ab.ca/access/performancefaq.htm

HTH.

[missinglinq]

Multiple threads I've seen over the years on half a dozen Access forums/newsgroups indicate that Access works well on local, hardwired networks (has real problems on wireless networks because of dropped connectivity problems) and ***terrible*** on wide area networks, showing huge skowdowns, just as you've apparently experienced.

Welcome to Bytes!

Linq ;0)>

[DonRayner]

From my past experience with Access over a WAN, you would be best to setup a terminal server at the database location and have the remote users RDC onto the terminal server to run the application.

I have seen database replication used successfully in the past but not without much work to reduce the probability of record duplication. It works best when you have satellite sites working on their own data and need access to non-realtime data from other sites or maybe you want all of the data available in a head office. The synchronization of the replicas would have to be done after hours and not in realtime to avoid locking users out of the database while the sync takes place.

You should change any autonumber fields to GUID to reduce the possibility of duplication and they can be a bitch to work with. You will get data conflicts where the same record has been edited on different replicas and if access can't resolve the conflicts you will have to manually intervene to resolve which replica has the correct information.

So that said, database replication and synchronization is really not worth the effort unless you are looking at hundreds of users spread over multiple sites. And if that's the case then access isn't what you should be using on the back end.