Moving Access application off a local server
We are a small charity. Our IT systems are working well but are now rather dated and I am looking at what would be involved in moving everything into the cloud.
Currently we have an office-based server with traditional shared files as you would expect. We also have 3 custom developed Access databases for different projects. It is these databases that are a concern to me, as they rely on running on our server. We do use them externally via RDP. But that currently relies on us maintaining a server.
The Access databases I would describe as non-trivial. They have tables, queries, VBA etc. Redesigning the front ends is not really an option that I want to consider at the moment.
We do have a Microsoft 365 business licence that we are currently only using for email. I would like to use that in some way – and it seems that for a file server it is pretty straightforward
So – what can anyone advise as to a strategy? I have done some research and most articles that are entitled “moving an Access db to the web” really just describe moving the backend tables (eg to Sharepoint).
Any help or advice would be welcome.
Regards
Richard
ASKER
Hi Kyle
Thanks for the quick feedback.
We have three databases. The most complex has 22 tables, 186 queries, 111 forms, 72 reports and 5 macros.
There is quite a lot of VBA logic involved in some of the transactions.
That's why I am not looking to re-write them.
Yes - I am just looking at the moment but I would like to put all of our systems onto the cloud and get rid of the server altogether eventually. It's just "blue-sky thinking" at the moment.
I have been looking at a hosted desktop solution which would probably be the easiest to implement as it would just be a case of moving everything from one place to another. But I would rather have a cloud solution if possible.
Thanks
Richard
Thanks for the quick feedback.
We have three databases. The most complex has 22 tables, 186 queries, 111 forms, 72 reports and 5 macros.
There is quite a lot of VBA logic involved in some of the transactions.
That's why I am not looking to re-write them.
Yes - I am just looking at the moment but I would like to put all of our systems onto the cloud and get rid of the server altogether eventually. It's just "blue-sky thinking" at the moment.
I have been looking at a hosted desktop solution which would probably be the easiest to implement as it would just be a case of moving everything from one place to another. But I would rather have a cloud solution if possible.
Thanks
Richard
A hosted desktop is a cloud implementation IMO, and gets rid of the goal of getting rid of your server. Someone else is responsible for maintaining the hardware, you just run the software.
I guess what differentiates a cloud solution versus a hosted desktop solution in your mind?
I guess what differentiates a cloud solution versus a hosted desktop solution in your mind?
2 Options :
- 1st option : the expensive one, rent a windows VPS complete the RDP licenses + Office...the benefit ...you move your applications right here , right now and you start working immediately ...the problem is the cost
- 2nd option : the cheap one : you rent a Linux VPS for a mere 3€/mo (more or less) and you get a nice MySQL server with 20 gb space and 1 gb...performance is good...but you will need to convert all your tables to mySQL equivalents with all the ups and downs...great pro, today the FE is Access ...tomorrow is Web interface, the day after tomorrow the application runs on your mobile.
Unfortunately access is a dying application.There is a difference between "dying" and someone wants to kill you...Access for many many years doesn't fit in Ms Plans but like another JohnMcLane is just too hard to die
What you want is a hosted RDP server in the cloud to replace your on-premise server. You would RDP into that.
The other option is to have a server in the cloud, then use WVD (Windows Virtual Desktop), which you get a license for with Office 365, and use that. In this way, you would not have an RDP server. Just one server in the cloud for file sharing.
As far as some of the other things that have been said, I disagree with Kyle on this:
"Unfortunately access is a dying application. "
and I'd like to know why he thinks so. Microsoft continues to bring out new versions, and has made substantial improvements to it. They have also announced that there will be a 2022 version.
Now if you say that desktop apps are dying out, I would tend to agree. But certainly not Access specifically. Given that there will be a 2022 version, you could be running your apps till 2032 without issue.
John said:
Well cost is relative; re-writing the app is costly too (even partially to use something like mySQL Server), much more so than setting up a RDP server in the cloud. Since you are already doing this on-prem, it literally is plug and play. You app won't need to change one iota. It also should be pointed out that Access is not supported on anything other than Windows, so you're going to end up with Windows anyway.
Plus, you have the alternative of WVD, which means no RDS licensing costs. All you need to do is put up a server to host the data.
Jim.
The other option is to have a server in the cloud, then use WVD (Windows Virtual Desktop), which you get a license for with Office 365, and use that. In this way, you would not have an RDP server. Just one server in the cloud for file sharing.
As far as some of the other things that have been said, I disagree with Kyle on this:
"Unfortunately access is a dying application. "
and I'd like to know why he thinks so. Microsoft continues to bring out new versions, and has made substantial improvements to it. They have also announced that there will be a 2022 version.
Now if you say that desktop apps are dying out, I would tend to agree. But certainly not Access specifically. Given that there will be a 2022 version, you could be running your apps till 2032 without issue.
John said:
1st option : the expensive one,
Well cost is relative; re-writing the app is costly too (even partially to use something like mySQL Server), much more so than setting up a RDP server in the cloud. Since you are already doing this on-prem, it literally is plug and play. You app won't need to change one iota. It also should be pointed out that Access is not supported on anything other than Windows, so you're going to end up with Windows anyway.
Plus, you have the alternative of WVD, which means no RDS licensing costs. All you need to do is put up a server to host the data.
Jim.
and I'd like to know why he thinks so.
As you have stated desktop apps are dwindling and while support for the application may be available skillsets surrounding the access haven't been the greatest. Further, look at the number of jobs available for access developers. Lastly based on my experience with access and its limitations versus what can be done in true N-Tier applications is the last reason I would say it is dying.
Can you use Cobol or Pascal? Sure - but would you argue that they aren't also dying?
Perhaps dying was the wrong word to use - but I certainly wouldn't start any new projects in access and would steer anyone away from doing so as well. I recognize MS will continue to support it but I don't see a viable future for access long term. Again, just stating my opinions.
Well saying the desktop is dying is something different, and that is certainly true. It's becoming a web/mobile world. But the desktop is not as dead as Microsoft and everyone else would like you to think. There's still many places in the US and elsewhere where good internet connectivity is lacking.
In this case though, it would be silly to give up on something already written simply because it is written in Access. As I mentioned, a 2022 version will be released, Microsoft continues to make fundamental improvements in it, and there is no product from Microsoft or anyone else that can yet fill its shoes. Power Apps/BI are getting there, but they can't do the job yet. Access will be around for another ten years at least.
So I believe Access still has its place, even for new projects. Not every app needs to be a n-tier app, along with the time and cost that go into developing something like that, especially when it comes to custom one-off software. Access makes a great front end for mySQL or SQL Server, it's easy to work with, and it connects to just about everything.
The major downside to Access is its sensitivity to its run environment, so no, it's not something you would use with thousands of seats, but then it was never designed to be that either.
Jim.
In this case though, it would be silly to give up on something already written simply because it is written in Access. As I mentioned, a 2022 version will be released, Microsoft continues to make fundamental improvements in it, and there is no product from Microsoft or anyone else that can yet fill its shoes. Power Apps/BI are getting there, but they can't do the job yet. Access will be around for another ten years at least.
So I believe Access still has its place, even for new projects. Not every app needs to be a n-tier app, along with the time and cost that go into developing something like that, especially when it comes to custom one-off software. Access makes a great front end for mySQL or SQL Server, it's easy to work with, and it connects to just about everything.
The major downside to Access is its sensitivity to its run environment, so no, it's not something you would use with thousands of seats, but then it was never designed to be that either.
Jim.
Just to clarify something... because too many "solutions'...VWD is essentially a cloud hosted RDS more or less (if you check it mentions RDS license as case of eligible license)..you just get a turn key solution (Office)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well yes and no : https://azure.microsoft.com/en-us/pricing/details/virtual-desktop/
Anyway all these are just RDS derivatives more or less...in the end the story is pretty much the same ..just a remote machine somewhere in a Datacenter...the licensing is different and of course the naming.
So for the OP ...is just 2 solutions..a Windows machine that it should cost you some good money unless you fall in one of the "eligible" categories and you just copy the application/s and besides the Remote Connection it would feel and works as it was (even better maybe .. .depending on the design) and a dead cheap Linux machine that will cost you almost nothing but it would require some (or maybe more than some ) work to get it to functional level...
There is no additional cost if you have an eligible Windows, Microsoft 365, or Microsoft Remote Desktop Services (RDS) Client Access License (CAL).So there is some kind of association
Anyway all these are just RDS derivatives more or less...in the end the story is pretty much the same ..just a remote machine somewhere in a Datacenter...the licensing is different and of course the naming.
So for the OP ...is just 2 solutions..a Windows machine that it should cost you some good money unless you fall in one of the "eligible" categories and you just copy the application/s and besides the Remote Connection it would feel and works as it was (even better maybe .. .depending on the design) and a dead cheap Linux machine that will cost you almost nothing but it would require some (or maybe more than some ) work to get it to functional level...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I didn't notice the "Microsoft 365 business licence" but again is not 100% that are eligible for VWD...
Here are the elligible :
Microsoft 365 E3/E5
Microsoft 365 A3/A5/Student Use Benefits
Microsoft 365 F3
Microsoft 365 Business Premium**
Windows 10 Enterprise E3/E5
Windows 10 Education A3/A5
Windows 10 VDA per user
Here are the elligible :
Microsoft 365 E3/E5
Microsoft 365 A3/A5/Student Use Benefits
Microsoft 365 F3
Microsoft 365 Business Premium**
Windows 10 Enterprise E3/E5
Windows 10 Education A3/A5
Windows 10 VDA per user
ASKER
This whole discussion is most interesting. Currently my mind is completely open. We don't have to do anything right now (maybe not for years), so I have plenty of time to get all my thoughts straight.
From what I have read above, and relating it to our own situation, I am thinking that a hosted desktop solution of some sort will be the best solution eventually.
We currently have
Desktop licences for Access (LTSC 2021)
Microsoft Remote Desktop Services (RDS) Client Access Licences (CAL) - (not sure how many though if they are user-based) - maybe not enough for everyone.
I am now going to concentrate on researching WVD to see what transpires. Being a charity we cannot basically afford anything, so that limits our options!!!
Thank you for your advice - I welcome any further comments.
I will apportion credits next week.
Best regards
Richard Tomalin
From what I have read above, and relating it to our own situation, I am thinking that a hosted desktop solution of some sort will be the best solution eventually.
We currently have
Microsoft 365 Business Basic (Nonprofit Staff Pricing)
Desktop licences for Office apps (LTSC Standard 2021)Desktop licences for Access (LTSC 2021)
Microsoft Remote Desktop Services (RDS) Client Access Licences (CAL) - (not sure how many though if they are user-based) - maybe not enough for everyone.
I am now going to concentrate on researching WVD to see what transpires. Being a charity we cannot basically afford anything, so that limits our options!!!
Thank you for your advice - I welcome any further comments.
I will apportion credits next week.
Best regards
Richard Tomalin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I found the perfect solution to this issue 2 years ago.
I have a 5 user Access application (complete summer camp system). each user is geographically separate.
I signed up with a company in California: RemoteAppHosting. There are others that do the same thing.
Each user has their own partition and copy of the front end. Cost per user is $25 per month.
Works perfectly. Service (if needed, is prompt).
Total setup and installation was one hour. I am not kidding. Connection is RDP. Speed is great.
This solution is a no brainer. Not one change to the Access application.
I have a 5 user Access application (complete summer camp system). each user is geographically separate.
I signed up with a company in California: RemoteAppHosting. There are others that do the same thing.
Each user has their own partition and copy of the front end. Cost per user is $25 per month.
Works perfectly. Service (if needed, is prompt).
Total setup and installation was one hour. I am not kidding. Connection is RDP. Speed is great.
This solution is a no brainer. Not one change to the Access application.
ASKER
Thank you all for your input. It has been most useful.
One question that was posed - how do I define a "Cloud solution"?
My meaning I think is really that a true cloud solution runs from any device connected to the Internet. Remote Desktop Connection needs to have setup on the client device, so is not completely mobile.
Best regards
Richard
One question that was posed - how do I define a "Cloud solution"?
My meaning I think is really that a true cloud solution runs from any device connected to the Internet. Remote Desktop Connection needs to have setup on the client device, so is not completely mobile.
Best regards
Richard
Well...Cloud is more a marketing trick ...IMHO.
Anyway...pretty much every device has RDP if this is the crucial point ...so don't worry about the app that does the connectivity (it' available either you are on desktop , or on your mobile) ... the 1st only real concern is the license and the 2nd the usability.(doesn't expect on your mobile to work comfortably on a desktop oriented platform)
Anyway...pretty much every device has RDP if this is the crucial point ...so don't worry about the app that does the connectivity (it' available either you are on desktop , or on your mobile) ... the 1st only real concern is the license and the 2nd the usability.(doesn't expect on your mobile to work comfortably on a desktop oriented platform)
<<One question that was posed - how do I define a "Cloud solution"? >>
Anything that is not on-premise and you do not own or manage the software or equipment.
One thing to understand with "Cloud" is that there are three basic things/ways involved:
1. Host your servers, meaning they control the equipment, but you manage the server itself.
2. SAAS - Software As A Service. For example, SQL Server, which was Microsoft first cloud offering, and what originally was refered to as "Azure"
Now "Azure Services" refer to Microsoft's entire cloud offerings, be it Active Directory, SQL, a pre-configured server, or a server you manage yourself.
3. A web service that does what you want (i.e. run Office).
<<My meaning I think is really that a true cloud solution runs from any device connected to the Internet.>>
Even with RDP, that's possible through a browser. You don't need the desktop client, but the desktop client is easier if it's available for your device.
Jim.
Anything that is not on-premise and you do not own or manage the software or equipment.
One thing to understand with "Cloud" is that there are three basic things/ways involved:
1. Host your servers, meaning they control the equipment, but you manage the server itself.
2. SAAS - Software As A Service. For example, SQL Server, which was Microsoft first cloud offering, and what originally was refered to as "Azure"
Now "Azure Services" refer to Microsoft's entire cloud offerings, be it Active Directory, SQL, a pre-configured server, or a server you manage yourself.
3. A web service that does what you want (i.e. run Office).
<<My meaning I think is really that a true cloud solution runs from any device connected to the Internet.>>
Even with RDP, that's possible through a browser. You don't need the desktop client, but the desktop client is easier if it's available for your device.
Jim.
From here:
https://answers.microsoft.com/en-us/msoffice/forum/all/using-ms-access-on-onedrive/880e752f-9d66-41d9-838e-d80e45ba5a17
It is possible to store the access databases in one drive. However you should not run them from one drive. You would need to check the file out, store it locally, run your processes, and then re-upload the file once done. This means that only one user could be in the database at any given time.
Another option would be to get an azure remote desktop server or something like that - essentially host a RDP server in the cloud where you can store your files. TCO will actually go up, but you relieve the headaches of relying on your infrastructure / server.
https://azure.microsoft.com/en-us/pricing/details/virtual-desktop/
On rewriting the database - it may be easier than you think:
How many tables / queries are you talking about? Is there any complex logic involved?
Lastly what's the reason for the move? Are you looking to get rid of your server all together?