Hello, I am new here and needed a little help if you wouldn't mind.

I've built an application for a company that I also use for other work I do for them. I have several tables, queries, forms, and a few reports. I have a lot of VBA code behind the scenes. The application is about 6 mbs. When accessing it over the VPN the application runs really, really slow. I need to find a way to increase the speed of the application so that everyone can work in it without the huge delays. Obviously, it runs normally locally.

Bringing data across the network is slow. You might be able to optimise your queries in such a way that less data is transferred. It can help to break a query down into several smaller ones, running simpler selection criteria first to cut down the number of records returned before you wade in with more complex criteria.

The Access Developer's Handbook has a good section on query optimisation.

Remember that the fastest WAN runs at about 10% the speed of a LAN. You may need to look into Citrix or similar products before you get acceptable performance. over a WAN.

I run TS over VPN tunnels, not only do we use MS Access but we also use 14,000+ images, the later creates the problems.

We have a dedicated Terminal Server linked via 1Gbit NIC card to the File Server. There are Front Ends on the Terminal Server pointing to Data on the File Server.

The broadband on the Server side has a 2MB SDSL line, the second London Gallery has a 256/2Mbit ADSL with a 20:1 contention. New York will be going to a SDSL T1 line as it is the only way we can get performance latency guarantees. This is seems the best option in the US.

The last remaining factor to investigate is large emails killing the broadband particularly asymmetric services and the broadband line is being shared with the mail server.

In essence we found that addressing the up speeds upgrading from 256bits to 2,000bits per second yeilded signicant performance gains.

The other issue with VPN I have experienced is depending on the ISP (no names) the number of hops between two sites can also impact performance. We are currently being bounced along the eastern seeboard but the new line should reduce the transmission rates to 85 millisecond between London and New York.

The last issue is slightly andecdotal but removing tracking Cookies on clients seems to improve performance, it stops these cookies running home to mummy and banging away on the bandwidth.

We run Access 97 on Windows Server 2003 - all Clients are XP. It does work and forms with ActiveX images take less than a second to load.


I hope this is helpful.

Simon

Well, nobody else has said it and you didn't mention it either.

If you haven't already done so, look into issues such as Front-End / Back-End (FE/BE) splits. There are wizards and help files in Access for this, plus maybe just short of a gazillion forum articles available via the forum's SEARCH feature.

In summary, you must realize that Access treats its location as a FILE SERVER, not as an application server. The app runs on your workstation no matter where everything resides. This means that every form, every report, every module, every last bit of interfacing and descriptive information has to be loaded to your terminal.

By moving data tables into a BE file and putting all else into an FE file, then instructing your users to download the FE ONCE (per new version of the FE), you cut out all the metadata and form/report data loading over your network. Instead, you make that part local. Can't do anything about the tables unless you wanted to invest in one copy of SQL server, which is a big step now. (I know you won't do it now, but keep it in mind if your database starts to grow a lot faster than you first expected.)

Now, another way to optimize this situation is to look at the structure of your tables. If they have a LOT of descriptive text data, just remember that the entire table has to come down to your workstation for filtration. So if there is a way to split the tables into a one//one pair such that you don't need both parts for everything, you reduce the amount of data you have to pull when you are doing some of your work.

Another way to handle this is to build a lot of lookup tables used for translation of a code into text if that is appropriate to your layout. Sometimes it isn't appropriate, but where it is, you can often save time by not translating the codes for anything except reports.

This next is considered as a possible but somewhat complex possibility. If you have a lot of relatively static lookups (see above), make a FEW tables local to the FE so that they get downloaded and used locally on the workstation, leaving the tables holding the lookup code-values to be downloaded.

Access tends to not be batch-oriented, but there can be certain cases where a heavy lot of processing can be done via stored action queries - delete, insert, & update actions. Or sequences of same controlled by a macro. If there is a case to be made for such processing now and then, consider buying one more copy of Access for the server and looking into the Windows Task Scheduler as a way to trigger this automatic (or at least not necessarily interactive) processing. Look in Access Help for Command Line options and in particular at the /X option that lets you trigger a macro. If you have a sequence of queries and code actions elaborated in a macro that includes a QUIT action as its last function (to close the .MDB, of course), you can fully automate anything that doesn't require hand-sequencing. Stated another way, the computer can remember not only data but actions. Make it part of your job to "teach" the computer what it needs to do for you at the macro level. I wish I could say no pun intended, but people who know me would end up ROTFLTAO (which is the plural of ROTFLMAO.)

The whole concept can be understood by remembering that Access is running on remote data but still needs to see everything. The bigger the tables you need to use, the more data you are transferring. The more data you must transfer, the harder you hit your network.

Good luck, it isn't a trivial undertaking. But at least some tools are there.

Fair comment Doc_Man. In my response I was assuming the database was already structured FE/BE, but reading the post again I can see that may not be the case.

What great information. The FE/BE is one that I considered but didn't make a big difference for me. I am going to take what I've been told and try to apply it. I am a newbie at this and this is my first client. I really appreciate all the help I've received. Based on what I am good at I hope that I can too offer some help to someone that needs it.

The FE and BE are important, you should get a little speed degradation using VPN but afterall all TS is doing is a screen dump. Windows 2003 TS is fine but Windows 2002 utilises 256 colours - whoopie!

Looking at this again, please be VERY careful. Using TS to open Access on a single server is a case that needs to be reviewed. If you have individual copies of Access for each potential user who will go through TS, you might be OK. If you contacted Microsoft Corporate Sales and got a corporate license for Access, you might be OK. But if you only bought one copy of Access for the machine on which you are running TS, you are almost certainly in violation of the End User License Agreement. You and I talking through the forum will never solve this question. Only you and your tech staff can answer. But if you read the EULA very carefully, there are parts that talk about USERS, not computers. Allowing TS usage for multiple users at once violates either paragraph 2 or paragraph 4, I forget which, of the "standard" MS EULA - and I'm a little too lazy to go back and look up it.

Having now said what I said so that the ball is in your court, I can honestly wish you luck on this, knowing that many articles on CITRIX and other users of TS have cropped up here in the last several months. At least, more than I'm used to seeing. You might wish to search this forum for topics such as CITRIX and TERMINAL SERVICES and the like. Many folks have had major headaches with this environment.