Microsoft Access alternative when using Excel as a front-end

[mikeymay]

I have always used Access to store data where an Excel tool requires multi-user access and data to be written and queried.

When my clients started using SharePoint (SP) I had to get them to use an Azure Data Blob to provide a virtual server as Excel can't talk to Access via web addresses. By placing the Access and Excel files in the Data Blob location and using a shortcut to the Excel file in the SP platform, I was able to keep using the Excel - Access - Excel set up.

I'm now thinking that I should start to use a web based data base that Excel can talk to via VBA but I have no idea what to use.

Could anyone recommend something to use that is easy to use as I'm not brilliant with Access outside the scope of setting up tables, related tables, writing to and querying from the Db.


TIA

 

[bobsan42]

Basically anything ... MariaDb, MySQL, MS SQL Server, .... you can even use web APIs to get and post data. It depends what's readily available or easiest to setup and maintain.

First I was doing everything in Excel. Then I moved to Access to resolve multi-user access and data size problems.
Then I split my projects in back-end(s) and UI (front-end) in Access to resolve design/distribution problem.
But the limitations of the RTE and some feature differences with full Access version made it quite a burden.
Then I moved the front-end to excel, keeping Access files as back-end. And it worked well for some time in LAN, but too slow over VPN.
However, after some time I decided that Access files are too clumsy, slow and unreliable in a multi-user environment. Also designing a user management system is not necessary when you have it built-in you server.

I am currently using a mariaDB server installed in the LAN, compatible with MySQL. And I must say I have no stability issues and the performance is times faster. And a much bigger performance difference over VPN. The server does most of the work.
The drawbacks (regardless of the chosen server and only if you want to transfer existing projects to a server environment):

• replicating/moving your data/existing projects to a server is a bit tedious task and not necessarily straightforward
• rewriting your SQL queries. SQLs are not 100% compatible between servers - slightly different syntax, different built-in functions.
• some code changes will probably have to be made

But I see no drawbacks for new projects. Only benefits.