Today, I thought I’d briefly touch upon a more and more common question, which is:
How can we share a Microsoft Access database online between multiple users?
In today’s world, sharing data is simply a necessity! So it is only natural to have the need to share Microsoft Access data with people outside of your network, over the Internet…
I previously posted about this issue in my article:
Today, I wanted to continue the discussion a little more.
So How Can We Do It Exactly?
The first thing which is critical to understand is that Microsoft Access is absolutely not meant to be shared via the Internet!
Now, yes, there are a number of technologies one can employ to enable remote access, I’m thinking of TeamViewer, RDP, CITRIX, …
There’s also the option of converting things over to SharePoint. However, this would never be an option I’d truly consider (more information below)!
The reality is that if you want to have people from different locations needing access your database, then you are best to migrate it, the back-end at least, to a proper Web Database. So forget SharePoint and all its limitations and instead look at turning towards something like:
✔️ Azure SQL
✔️ MySQL
✔️ PostgreSQL
❌ DataVerse
These will serve you best!
There exist a number of tools on the market, some free, that can automate the migration process and then you can merely re-link your tables and have a fully functional system that can be shared by individuals anywhere in the world.
Whether you also choose to convert your front-end to a proper web language (PHP, .net, …) requires serious evaluation based on performance, cost, … and other wants &needs.
What I can say is I’ve migrated several database to Azure SQL, MySQL & DataVerse and in general performance remained quite acceptable. Obviously, you need to ensure you have the necessary computing power (disk space, memory, cpu, bandwidth) to support the user load.
Azure SQL, DataVerse, or any Microsoft offering is costly in comparison with a MySQL or PostgreSQL hosting of a database. That said Azure SQL performance, in my experience, was superior to MySQL, but as I said, it comes at a cost and that cost is orders of magnitude higher. Let me put things into perspective. I had a client running a MySQL db at 5$/month on a shared hosting plan that ran their entire business system. Because there were some performance issues they migrated to Azure SQL at 120$/month. As time has gone on, capacity has increased and so has their costs as they are now over 250$/month. With hindsight, they probably would have been much better off with their own VPS running MySQL for a fraction of the price (30-50$/month or so)! It wasn’t my decision in this instance, and I was simply along for the ride to ensure a smooth system transition, but the experience did permit me to learn about Azure. I will however add that although Microsoft Azure’s offering are good, they are EXTREMELY complex to navigate and manage. The menus, figuring out licensing, where to go, what to do is NIGHTMARISH!!!
I personally do not advise in using DataVerse! I’ve written a number of articles about my experiences with DataVerse and it is not something I’d ever recommend (see resources below). IMHO, if you are going the Microsoft route, you are far better off using Azure SQL and having proper control over things with a proper interface which you can connect SSMS to…
One must also understand that the performance of a linked Access database to any online web database will ALWAYS be slower than a web interface built on top of a web database. There is simply no way around that fact. This is also why it is normally critical IMHO to always determine if Internet/Web/Remote access is required of any system you are developing to determine at the initial stages if Access truly is the proper tool for the job.
All of this to say, there are a number of very good options available today, but take the proper time to evaluate them and compare them and be sure to evaluate medium to long term how cost will rise. Once you take the dive it is normally very complicated and costly to switch again so it is important to get things right the 1st time.
Lastly, let me reiterates that sharing Microsoft Access databases via OneDrive, Google Drive, DropBox, … is an absolute no-no! Same thing within an organization, sharing via SharePoint document libraries is another absolute no-no. I’ve had to clean up the mess of these types of setups and let me state formally: Save yourself the pain and Just Don’t Do It!!!!
So to summarize all of the above, and based on my personal experience, if you are looking to share an Access database with people in different locations simultaneously then you should migrate the back-end to a web database, such as:
✔️ Azure SQL
✔️ MySQL
✔️ PostgreSQL
✔️ Or another proper web database
and re-link the front-end to the web database.
Stay away from anything like:
❌ SharePoint
❌ DataVerse
❌ OneDrive, Google Drive, DropBox, … (any syncing system)
DataVerse Resources
I know DataVerse is the ‘new’ thing out right now and lots of developers are eager to be on the cusp of technology. So, if you truly want to go down that path (ignoring my advice) I urge you to at least review the following before taking the dive:
