With many of us working from home, here are some helpful tips based on our experience about running your Access database(s) remotely. Our customers have many different set-ups, but those working over a VPN to connect to network drives in the office probably suffer the most with a change to remote working when trying to use an Access database system this way.
So, if you find that your Access database system crawls along way too slowly from home, please do read on.
When you are running an Access database over a VPN, you are dependent on your own internet connection plus the network in the office for sending data to and fro between you and the work server. This is a slow process, unfortunately. You will find that things improve marginally if you have a local copy of your database front end file and then only rely on the connection for reaching the back/data file, but it still almost certainly won't be great. And for many that's an understatement. If you would like to see ways to improve performance, please take a look at the list of tips below. It provides a simple guide to some alternative ways of operating and explains the pros and cons of them. We will keep the page updated as we come across any other solutions people have implemented successfully.
How to solve speed issues with an Access database when working remotely from home
- Make sure your database is made up of 2 linked files - one for data and one for the application file. This is commonly called Back End/Front End.
- Make sure each user has their own, local copy of the Front end file. This reduces network traffic and reduces the likelihood of data corruptions regardless of the rest of your set-up. Note that the Back (data) file is always centrally-held and shared.
- Make sure your data file is backed up regularly. There could be tears if it isn't!
- Avoid wireless connections to your data file, if possible.
- A connection over the internet to your office server with or without the security of a VPN, whilst you are still running the Microsoft Office applications on your own computer is a wide-area network (WAN). Access does not work well with WANs and you are likely to suffer with poor performance and speed issues.
- A short-term or intermittent option for working remotely is to log into your PC at the office using remote control software, e.g. TeamViewer. Your PC in the office needs to be left switched on, but you end up using it as if you are sitting at it.
- By far the most speed-efficient and secure way to operate an Access database remotely is to use a remote desktop (known as RDP, for remote desktop protocol) connection, secured via a VPN. In this set-up you are logging into a server where you see a Windows desktop as if it is your own computer, but actually everything is running directly on that server. This means that no data transfers across a network, and your Access database will fly along even with 10s of concurrent users. Note that the server needs to be set up to cope with the appropriate number of remote, concurrent users.
- The remote server you log into could be the existing server at the office, or ...
- ...if you are going the whole hog and abandoning the office altogether, it can be a hosted desktop in a virtual office. There are companies that can provide you with a web browser interface that takes you to your very own Windows desktop in the Cloud as if it is on your own computer. YourOfficeAnywhere is one company we have used with some clients.
- An alternative solution that works well in many cases is to migrate your data file to SharePoint Lists (part of Microsoft 365). Your data sits in the Cloud, hosted by Microsoft, and you connect to it from anywhere over the internet from your Access application/Front End file.
So weighing up the pros and cons of those 3 possible set-ups for on-going remote working:
RDPs own server
|✅ fast||❌ need suitable server|
|✅ need to secure it yourself, e.g. VPN|
✅ ❌ cost depends on current server and existing software licences
RDPs hosted server
|✅ fast||❌ cost is per user per month so can be expensive|
|✅ don't need your own server|
|✅ access via web browser and secured for you|
|✅ scaleable though performance may reduce with more users||❌ poorer speed and reliant on good internet connection|
|✅ no server needed|
|✅ secured via logins at Microsoft|
We can help you adapt your existing database system to work well remotely or from home. Please feel free to contact us for advice. We give free initial consultations.
Don't just take our word for it...
"I would like to say thanks to yourself and your team for all your hard work.
We are very happy with the quoting system, for the first time ever we are looking at a system that we have confidence in."
Steve Ringsell, CFN Packaging