We help IT Professionals succeed at work.

MS Access backend on the cloud

SimonBoutin
SimonBoutin used Ask the Experts™
on
Hi,
I'm a MS Access developper who try to put my database backend to the cloud and link it to the frontend on my desktop. My client is a business which want to do that in order to access its data from everywhere (or at the very least from the company's intranet network).
I see blogs and I think the better solution is to use a Sharepoint site but I know nothing about Sharepoint nor to install it, configure it, etc.

1. Do you know if Sharepoint is the solution to my problem?
2. Do you know how can I get it?
3. Is there exist a better solution to access my backend with my MS Access frontend from the cloud?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
1  & 2  Sharepoint is not that good as solution based on the many posts i have seen
3. The best Solution is to have a Real Cloud Aware BE like MsSQL or MySQL...but it requires some work
If you want to have Access in the Cloud the quickest solution is RDP...you put FE + BE on a Cloud VPS running Remote Desktop Services and that's about all...
On each user you have to have a separate copy of FE.
You may also want to take a look at a similar question posted a few days ago : https://www.experts-exchange.com/questions/29166332/MS-Access-SQL-Virtual-Server.html?anchor=a42992815¬ificationFollowed=242150865#a42992815
There is also another solution but you need a rock solid Internet connection with extremely low latency....like having a VPS as a file Server on the cloud and VPN to it so that it looks that is on the "same" network...but there aren't too many good reports on that.

Author

Commented:
Hi John,
thanks for your fast reply.
VPS stands for "Virtual Private Server" right?
And what "RDP" stands for?
Note in the final solution we must have at least 20 simultaned users connected to the Cloud backend, is that possible with Remote Desktop Services?
Finally, is your solution simple to implement? Because I have no knowledge about that and the complete solution must be successful before 2020...
Thank you very much for your help!
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Access does not paly well with WANs which the cloud is.  Access is meant to be used over a wired LAN,

I'd urge you to read https://www.devhut.net/2016/09/24/access-back-end-location-wan-online-server-onedrive-dropbox/ which gives a couple options.

If your client has SQL Server already, or an Azure account, then you could upsize the back-end to SQL Server and go from there.  Even if they don't, you could install and use SQL Server Express.

Almost every solution will involve IT support to install/setup software, access, ... so a deadline of 2020 may not be feasible in any case.  It will greatly depend on IT and then you may need to make changes to the database as well.
John TsioumprisSoftware & Systems Engineer

Commented:
RDP -- > Remote Desktop
I know from a fact that the RDP scenario....works...i had a similar case in my old job and we had quite a few people working that scenario...but i haven't  tested the limits.....
Since you are a strict time schedule  my best bet is to rent a good VPS...setup RDS ...and just perform all the operations...the trial period should be more than enough to test just about everything...
If you implement the RemoteApp scenario...to the end users it would almost transparent...
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Before getting into complex scenarios, are all the implicated users connected through a common wired LAN?  If so, you should simply be able to split your database and deploy an individual copy of the front-end to each user to use, that's it.

You may like to review

https://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/
https://www.devhut.net/2015/06/30/ms-access-deploying-your-database-front-end-to-your-users/
ste5anSenior Developer

Commented:
My client is a business which want to do that in order to access its data from everywhere (or at the very least from the company's intranet network).
In the intranet, having a normal, stable LAN, you use the split database approach, where every user gets its own front-end copied to a local directory and the backend database file resides on a share of the fileserver.

Depending on the amount of data and the security targets, you need to consider using a RDBMS and use linked tables and views as well as pass-through queries, when necessary. Here the ideal back-end is SQL Server, but Oracle, Informix, Postgres and MySQL are also possible.

The question is: How much budget is allocated for the "everywhere" requirement? And does it include connected / disconnected access?

When the users are always connected, then you may use a terminal server to host the application, this gives the optimal experience between application performance and resilience.
When your client offers good VPN to its users, then a on-premises RDBMS works pretty well. In many scenarios better than entry-level cloud databases.
And the last thing which works connected everywhere: cloud database instances. But you need - at least for Azure and AWS - more than the entry level databases. Otherwise your application will have poor performance (experienced lag).
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Stay away from Sharepoint.

Azure can work, but unless you've created the application with client-server techniques that often means a full rewrite.

Your best option is to use an RDS system like Citrix or Microsoft RDS. You can support quite a few users on that scenario, and those users can be located anywhere.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Sharepoint is, in this respect, similar to OneDrive, so your question, and thus the answer, is very close to this question:

MS Access, SQL, Virtual Server

Likewise, consider using the method described in detail in my article:

Deploy and update a Microsoft Access application with one click

Author

Commented:
Thank you very much for your answers.
At this time I'm not sure which solutions are better until the project is implemented...
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
You need to write down your project specifications (number of users, connection types: wired/wireless, LAN/WAN, access needs: local only, remote, budget, ...) and only once you've properly defined all of that can you make an informed decision on the proper route to take.

Author

Commented:
Number of users = Max 20
wired/wireless = Both
WAN if possible, in the worst case LAN
Access needs = Read/Write
Not local only
Remote = if possible yes
budget = less than 2 000$ if possible

It's not an absolute certitude, but it's what we have in mind.
You're help is very appreciated, thanks!
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
WAN is a non-starter with Access, as others have mentioned multiple times. If you're not on a wired LAN, then the only real, reliable option you have is RDS/Citrix. If you're already running a Windows Server box then you have RDS capabilities, you'd just have to purchase licenses. Of course, you may have to pay someone to setup RDS for you, unless you have someone on staff who can do that.

If you don't have a Windows Server box, then your cost will almost certainly be well beyond 2k.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Truthfully, with requirements like that, (i) the budget doesn't match the project, (ii) I would never consider Access and would be looking at using proper web technologies (PHP, .Net, ...).

Did you ever review my original link?  In it a couple alternatives exists CITRIX, Terminal Services, using external hosting services  (for instance http://accesshosting.com/ - one of many, I have to affiliation), migrating the BE to Azure, but they all take time to deploy and in some cases the application itself needs to be adjusted to work properly.
John TsioumprisSoftware & Systems Engineer

Commented:
WAN is an option if the FE is carefully designed...in my older demo benchmark application :https://www.linkedin.com/posts/tsgiannis_a-small-demo-of-connecting-ms-access-fe-to-activity-6392696633531858944-dsuU
You will see a pulling of 1000 rows of records of a 120+ columns table in just 3 seconds...all these with a mere ADSL lines around 14mbps with 50+ ms latency on a dead cheap VPS (old deal 1€/mo) with a single Xeon shared XEON with 1Gb memory running MySQL
If you could get finer line with most importantly better ping the operation would be almost natural and if heavy queries are involved it would fly....but it needs works...quite some work.

Author

Commented:
Hi Daniel,
yes I reviewed your original link and take it into account.
I think now I have all info I was asking for.
I think RDS with Citrix is the way I should take.
I talk to my client and the first step will be to implement to a LAN, then early in 2020 we will check for WAN.
Thank you very much everyone for your advices.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Good luck with everything.