I need to increase my Access DB speed over a VPN

[pokerace]

I have a small Access database created with Access 2000, with multiple users accessing the mdb file over the office LAN. They also need to be able to use VPN and update the database, but it is too slow to be usable.

I've done some reading and it sounds as if there is a difference between the database engine for Access 2000 (JET) and the engine for newer version of Access, in that the newer versions use the same engine as SQL Server and would be much more efficient for using over a WAN.

Can I convert the Access 2000 database to a newer version, and then expect the WAN performance to increase drastically when users access the database over a VPN?

Is it that simple? Thanks.

 

[boblarson]

No it isn't that simple. See here for about using Access on a WAN:
http://www.members.shaw.ca/AlbertKallal/Wan/Wans.html

Also, your performance can vary due to your design, how people are accessing it, where your backend is located, etc. Does everyone work with a different copy of the frontend or are they all opening the same file from somewhere?

 

[pokerace]

They are all opening the same file from a network folder.

In the article you referenced, under the heading "Use a True Client to Server Setup", it sounds like upgrading the Access 2000 JET database to a new version of Access with the Microsoft Data Engine will allow Access to be run in "true client to server mode." If that is a possible solution, what do I need to do to change my JET based database to the MDE based database?

 

[boblarson]

That isn't MDE but MSDE (which now is SQL Server Express). You still need to look at your front end design because

1. It should not be being opened by multiple people from the same file (each should have a copy on their computer).

2. There are things on your front end which can cause major slow down (lots of combo boxes, list boxes, subforms, etc.) which need to be designed properly to limit the amount of data required at any one time.

3. and other factors too numerous to list for the time I have to answer the question.

Perhaps uploading a copy here (after removing any sensitive data, compacting, and then zipping) would allow us to make suggestions on speed enhancements.

 

[pokerace]

Which means that I need to split the database? I can do that and I can look into removing sensitive data and uploading. Though, it isn't a large database at all; Only 13 tables and about 250 client records.

As I understand the article though, my main issue right now is accessing the Access 2000 database over a WAN, which the article indicates isn't feasible because of the engine's design. Is that correct?

 

[boblarson]

As I understand the article though, my main issue right now is accessing the Access 2000 database over a WAN, which the article indicates isn't feasible because of the engine's design. Is that correct?

Essentially, yes. Working over a WAN without a split database is not only slow, but essentially fatal. It is HIGHLY prone to corruption. It really isn't a matter of IF it will corrupt, but WHEN it will corrupt.

 

[pokerace]

Ok, I tried to split the database and it is still too slow. I have compacted and zipped a backup of the unsplit db with sensitive data removed. It is password protected. I will send it to you bob and anyone who wants to take a crack at what my design issues could be.

 

[boblarson]

Ok, I tried to split the database and it is still too slow. I have compacted and zipped a backup of the unsplit db with sensitive data removed. It is password protected. I will send it to you bob and anyone who wants to take a crack at what my design issues could be.

1. Your zipped file isn't opening. I think you may have used Maximum Compression (which can cause issues). Can you do it again using Normal Compression?

2. Did you make sure to include your frontend with the backend?

 

[boblarson]

Okay, here's an issue I see which will cause you performance problems (regardless of whether it is split or not split, on a LAN or WAN).

You have 5 subforms on the main form. This can slow things down dramatically. I would suggest using a tab control to organize them and to allow you to load them as the user clicks on the tabs, so that you don't have to load and use the recordsources the whole time.

That is the primary thing that I see which will cause you (and your users) pain and suffering. If you go to loading the subforms when needed then it will help immensely.

Here's a sample from my website on how to load the subforms at runtime (for example on selection of a tab control):
http://downloads.btabdevelopment.com/Samples/subforms/Sample-Dynamic_Tab_Loading.zip

 

[shadow9449]

Pokerace:

I've had a lot of success deploying applications in multiple locations using a Terminal Server. I have one application that is accessed by close to 40 people in 9 physical locations (the office hosting the application has about 8 users on a LAN, the rest are using Terminal Server). Performance this way on an unsplit database was perfect until they had about 15 users at which point I split it which solved the problems. They kept adding locations and users and I think they had ONE problem with the server in March in the past year.

SHADOW

 

[pokerace]

Thanks for taking a look at the database bob and giving me something to try. Thanks as well to you Shadow for the tip about terminal server. I am going to explore both suggested options.

 

[pokerace]

Here's a sample from my website on how to load the subforms at runtime (for example on selection of a tab control):
http://downloads.btabdevelopment.com/Samples/subforms/Sample-Dynamic_Tab_Loading.zip

I've tried out your sample database and it has worked wonders! Now I am trying to understand it. Going through the code comments for the subform loading button, it seems that the subform must be added under the corresponding tab control, and then deleted, which will keep the links neccessary. Is that correct? Once deleted do I just add in a new unbound textbox and give it the same title that the subform had?