Combatting Latency Problems (Access (2003))

Hi all. I'm not as good with Access as with the rest of Office, therefore I need your help.

We have a database that is currently split into a front-end/back-end solution, with the front-end residing on the users' Desktop and the back-end residing on a file share. The main reason for this was to allow reports and code modules to be modified without having to take down the entire database. (It was also so a user wouldn't compromise the front-end for others!) Since then, the server on which the back-end resides have been moved several states away, creating an almost intolerable latency problem.

The initial approach to helping with this issue was to have users work with the database using a Citrix terminal server approach. (This entailed installing a front-end on the Citrix server which talks to the back-end on the same old fileshare.) This doesn't seem to be helping much, or at least not consistently. I've been brought back to investigate what can be done further to try and improve the situation.

My questions are probably naive, but I need to ask them anyway.

First, should I assume that putting the tables, forms, reports, etc. back into a single MDB file will help the situation, especially since we are going to maintain the TS approach?

And second, if we adopt a stricter approach to archiving and removing older data, would an export or append query be intelligent enough to maintain the relational aspect of the data? in other words, if we wanted to export a "customer transaction" to an archive table, would it be (relatively) simple enough to export the necessary data to update, say, both the "product table" and the "customer table"?

I apologize in advance for my lack of proper database training; like many others, I pretty much picked this stuff up "on the street."

<font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

Re: Combatting Latency Problems (Access (2003))

Sorry... I work in a pharmaceutical environment, where acronyms are a way of life! I was referring to maintaining the Citrix terminal server approach, where the processing would be done remotely. In other words, while splitting the database certainly had its advantages when the front-end was on the user's desktop and the back-end tables were on a fileshare, is it being counterproductive now that we have the Citrix server at our disposal? And if not, what is the idea configuration.

(And, out of morbid curiosity, why is it that Access seems to suffer from these latency issues more than Excel or Word? It's to the point where different areas of the company are setting up renegade servers just because the performance was SO abysmal when they moved the official servers from New Jersey to North Carolina!)

<font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike