Split Database and VPN - Microsoft Access Split Database Seminar


	Back to Access Split Database Seminar Comments List

Upload Images Link Email

Split Database and VPN

Eduardo Benaim

2 years ago

I Use a Split Database.
The Data Part (.accdb) (700 MB) file is located in a File Server in Location One. So it runs very Fast.
Location 2 is connected to Location 1 via VPN.
In Location 2, the Speed at which the Front End Loads is 20 times slower than that in Location 1.

Once it is Loaded the Response seems ok but not Optimal.

My Alternative was to go to the Web but with only 2 locations it seems the way to go.

Any suggestions on how to optimize this configuration? To make it faster in Location 2?

Kevin Yip

2 years ago

My advice is to migrate to an online SQL Server database, such as one offered by Winhost (Richard has a course on this) -- so that all users connect online to Winhost's much more powerful server at full Internet speed instead of connecting to your (possibly) less powerful server via VPN.  VPN uses encryption that slows down the Internet speed, sometimes a lot.  And Access is also not suited for online usage.  Winhost costs $5/month (paid annually at $60/yr), which is a small price to pay for having an optimal performance if that is important to you.

But even with Winhost, your Access front end needs to be designed for minimal data retrievals, because Internet speed is still not nearly close to LAN speed.  All your forms, modules, reports, etc. need to aim for minimal data retrievals by any user at any time.  In an online environment, you can't have a user browsing 10k records on the screen and slowing down everyone else.

I made a video on what it looks like to have an Access FE connect to a Winhost SQL Server database: https://www.youtube.com/watch?v=IJyKZRRWHnU .  My FE is somewhat designed for minimal data retrievals, but some tasks still take a long time.  Performance can vary greatly depending on the size of the data, the amount of data retrievals, the efficiency of the VBA code (if any), and above all, whether the user deems the performance acceptable or not.  One user may find a 10-second delay in a query acceptable, while another user may not.  Consider all these aspects when you start designing.  Nothing is perfect, so don't aim for perfection.  We often see a webpage take a long time to load, so this is commonplace on the Internet.

	Eduardo Benaim OP 2 years ago
Great. I like it. I agree. The best way to Go is Web. To go that way I have to completely redesign my Database. It is something I do want to do in the Future. For Now I use a Split Database where the Data (Data.accdb) is kept Local at the site where most users are. I like your Video. Thxs

	Eduardo Benaim OP 2 years ago
The VPN is a Quick Fix until I have a Chance to redesign the Database to run in the SQL Server. I just want to Optimize it.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Split Database Seminar.