Using Microsoft Access databases is becoming a problem for all companies that want to use file storage in the Cloud.
Many companies and professionals base part of their organisation and operations on Access. These are files that they have been keeping for years and that they have been adapting to their management needs.
Without a doubt we can say that this tool has been one of the best Microsoft products of all time. Will they be able to continue using this fantastic tool in the Cloud?
Many have already given up. For years they have been sharing their Access files on the company network. Now it seems that having those files in the cloud is a problem, because users can no longer connect to the database and work directly without conflicts or file overwriting.
Why Access does not work in the Cloud?
To say that it does not work without any nuances is not true.
Yes, there are ways to use it in the cloud, but they are not what the user expects.
Everyone expects to upload the Access file to the cloud and use it in the same way as they have been doing for the last few years.
If the file is shared, users expect to be able to open it, save their changes and have the database updated with the changes.
Access databases are designed to work on local networks, on internal company networks. They work in LAN (Local Area Network) environments.
The company’s internal network uses a SMB (Server Message Block) protocol that allows files or printers to be shared. Whether we are connected by cable or Wifi, this protocol has nothing to do with the Internet.
Access cannot work on the Internet in the same way as sharing a folder on the company’s internal network. No cloud storage platform works in the same way as the company network.
For practical purposes, sharing a folder on an internal network provides direct access to the file as if it were on your hard drive, it is almost equivalent. This does not happen over the Internet, the internal communication protocols of a network are very different.
First attempt. Using a synchroniser such as Dropbox or Drive.
This will be ignoring all the issues of security, data protection or whether it is a suitable solution for businesses. Many try what they know. They try to manage Access with popular synchronisers such as Dropbox or Drive.
If you try it on a one-to-one basis, a single user, it will apparently work. The file is opened, changes are made and it will be uploaded to the Cloud.
As soon as it is shared with another user, the problems typical of synchronisers appear. File conflicts or conflicting copies occur.
Changes made in Access will never be saved on the file itself and will produce a conflict in the following cases:
When two or more users make changes at the same time.
If modifications are made offline and another user has worked with the file.
If the file is simply open on another user’s computer. Any changes made by others will cause this situation.
In any case, this experiment of testing Access on a synchroniser, we don’t see it as wasted time, you learn that synchronisation is not for multi-user environments.
Someone may recommend you to separate the Access tables and put this part as shared in the synchroniser. Initially it will seem to work but there will come a time when you will lose records and conflicts will occur.
Conclusion: Synchronisers like Dropbox or Drive are not useful to have an Access database shared with several users.
Here is a link to Dropbox’s response to this question: Dropbox. Sharing Access Database
Possible solutions for sharing Access in the Cloud
[1] Using Dataprius
Dataprius is not a synchroniser, it is a multi-user system, as such it will not generate file conflicts. The issue is that it will not allow two people to edit the database at the same time.
If a user opens the database file and it is in use by another person, the typical «in-use file» window is displayed.
Dataprius «file in use» window
[2] Using Dataprius with MapDrive
When we set up a MapDrive mapped drive, it will function just like a shared network folder over the company’s internal network, i.e. SMB.
MapDrive Dataprius
Earlier we explained that Access databases were designed for multiple users connecting on the local network. That is why they did not work over the Internet. But this is something else, it works over the Internet but it is as if it were on our LAN.
Be careful, it is not a bed of roses, here we can face a speed problem. With small files and a very good internet connection, this can be a great solution. It’s just a matter of trying it out, and the guys at Dataprius will be able to help you see if it is the definitive solution.
[3] Setting up a VPN
A VPN (Virtual Private Network) is a solution that allows you to extend your local network. It is a costly solution in terms of implementation and resources.
We mention this possible solution because it could be suggested by some and we must include it for the completeness of the article.
A Dataprius mapped unit will require no installations or configurations: communications with the Cloud are encrypted in the same way as in the VPN. The mapped drive provides the same functionality as a VPN shared folder.
In the end the problem could be the same. You have to have a very good Internet connection, the speed at which Access updates can make these solutions unworkable.
Some Cloud providers provide VPN connection services. As it is a complex solution, we only leave this link that shows how to set up a VPN with Azure: Creating a site-to-site connection via Azure Portal.
[4] A Windows server in the Cloud
This is about setting up a Windows server in the Cloud.
For security reasons we will never recommend that this is done with a local company server or a NAS.
Opening local servers to allow connections from the Internet is reckless and can have catastrophic consequences for the company, it is only a matter of time. Only large companies with a permanent security staff can dare to do this, but not without continuous exposure and risk.
Internet servers. Separate them from the local network.
Having said that. We must set the server up with a provider that takes care of the security of the server for us.
The idea, then, is that we can hire a Windows server in the cloud to access Access files in various ways. There are providers that are GDPR compliant, where we can hire a Windows server:
– Clouding.io
– Upcloud
– Hetzner
There are basically two options.
- Set up a VPN to access a shared folder with Access.
- Enable remote sessions for users to connect.
Most commonly used are remote desktops. We provide this link from Clouding.io where they explain how to connect: Connecting to a Windows Server via Remote Desktop
[5] Using Sharepoint Service
Sharepoint is a Microsoft system that allows you to use Office documents in the cloud and other business functionalities. Of course you have the possibility to share Access databases although you have to make some transformations, it is not a direct use.
There are currently two ways to have a Sharepoint server in your company.
- Buy the licences and install it on an existing Windows server.
- You can contract the SAS service that Microsoft already offers in the Cloud.
Here is the link with the prices: Sharepoint Online options.
How to share Access databases: Create an Access database for sharing on the Web
Many of us think that Sharepoint is complex and expensive : Embark on SharePoint or the simplicity of Dataprius . Of course, there are many companies that use it.
[6] Linking database to tables in SQL Server
This solution consists of keeping the Access forms and having the tables stored in SQL Server.
You have to migrate the tables to SQL Server and then link them. This is usually a simple process because Access is compatible with SQL Server.
Appearance of SQL Server linked tables
As for the SQL Server, there are two options:
– Contract a Windows server in Cloud with SQL Server.
– Contract SQL Server in Azure.
Using the service provided by Microsoft Azure frees you from all the maintenance and security issues of a Windows server. The downside can be the price. A single database starts at about $200 per month.
This is undoubtedly one of the most powerful and viable solutions.
With this solution there is a very important qualitative leap. The database will now support thousands of connections and offer high performance. It will be expandable and has maintenance and possibilities worthy of large companies.
In short:
Access databases can connect to different data sources, they are not limited to just their own tables. The tables migrate to SQL Server and linked to these tables in Access.
The following links describe how to do this:
Migrating a database from Access to SQL Server.
How to Link an Access Database to SQL Server in Access 2016
Linking Access to our Azure SQL Database in the cloud
Migrate Access to Azure SQL Database
Linking Access Applications to SQL Server: Azure SQL DB (AccessToSQL)
[7] Linking database to tables in MySQL
This is a solution very similar to the previous one. The advantage is that it is cheaper and more versatile. You will have to hire a database service in the cloud such as Azure‘s or a hosting server where to install this MySQL, preferably a Linux server.
Link Access to MySQL
You will get a very high performance and features, almost without limits of users and connections. In addition, if the database is in MySQL, the basis for future extensions or developments has been established. We can say that an important standardisation step has been taken. With MySQL it will be much easier to set up applications, for example with PHP, which is one of the most widely used server languages in the world.
To link MySQL tables in Access you will need an intermediate element, an ODBC connector. This connector will allow you to have the MySQL tables linked in Access as if they were your own tables.
Of course, before linking the tables you have to migrate them to MySQL.
[8] Ordering a customised application
It may be best, in the end, to commission an application based on the old Access database management model.
Taking into account the dynamics of the database, a development company can build an application that meets expectations and is finally in the Cloud.
This is not so rare. Companies find themselves with the need to have their databases in the Cloud after growing their business model. Eventually all those Excel sheets and databases will end up becoming applications or growing into more integrated applications in the Cloud.
For those who have programming skills, there is this tool that allows you to integrate Access directly and in the Cloud: ScriptCase.
Other solutions
While browsing we have found companies that offer other types of solutions for having Access in the Cloud. These are almost always systems that convert the tables into their own system on which an application with forms works.
Most of these platforms are offered by companies in the United States. If the databases contain sensitive information or personal data, the General Data Protection Regulation (GDPR) is violated.
– https://www.accessdatabasecloud.com/ (NOT GDPR)
– https://www.caspio.com/ (NO GDPR)
– https://www.hyperoffice.com (NO GDPR)