Alex C
asked on
MS Access, SQL, Virtual Server
I need help in deciding path forward for our MS Access database.
Recently my old company was bought out by another and the new company changed the network topography. They removed network drives and went completely cloud base. We are using OneDrive platform. OneDrive does not support MS Access functionality at all. I cannot share our MS Access database with multiple users, because OneDrive replicates back-end for every user that links to it. so I needed to place back-end on every user’s computer. As result, I have several versions of the back-end and we do repeat inspections as result.
I am looking for a way to solve this problem, but also a good path forward with MS Access database, and I need your advice.
I have considered the following things:
1. Use a virtual server to host our Access database
2. Install MS Access 2010, change database to web apps and then host it on an older SharePoint server.
3. Use an SQL server, and migrate MS Access back-end to SQL or AZURE and somehow link it to the front end. I read about this online, but not sure how much work will be involved.
4. Completely redesign the database to SQL version or other version
5. Leave as it is and deal with it.
The company is willing to pay for the hosting, but I am also looking at path forward and future of this database. Its quite developed and very useful for many personnel.
I need your expertise advice since you all know ins and outs of other databases.
Thank you.
Recently my old company was bought out by another and the new company changed the network topography. They removed network drives and went completely cloud base. We are using OneDrive platform. OneDrive does not support MS Access functionality at all. I cannot share our MS Access database with multiple users, because OneDrive replicates back-end for every user that links to it. so I needed to place back-end on every user’s computer. As result, I have several versions of the back-end and we do repeat inspections as result.
I am looking for a way to solve this problem, but also a good path forward with MS Access database, and I need your advice.
I have considered the following things:
1. Use a virtual server to host our Access database
2. Install MS Access 2010, change database to web apps and then host it on an older SharePoint server.
3. Use an SQL server, and migrate MS Access back-end to SQL or AZURE and somehow link it to the front end. I read about this online, but not sure how much work will be involved.
4. Completely redesign the database to SQL version or other version
5. Leave as it is and deal with it.
The company is willing to pay for the hosting, but I am also looking at path forward and future of this database. Its quite developed and very useful for many personnel.
I need your expertise advice since you all know ins and outs of other databases.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. If you use the "pro" version of Windows, the RDP license is free.
Don't let licenses get in the way. Whoever made the decision to move to the cloud had no clue what they were doing and caused this mess. Rewriting the application as a web app will cost thousands upon thousands of dollars. Development time will be at least 5 times what it took to develop the Access app originally.
Since everything is cloud based, Citrix is your simplest solution. This will probably not require any changes to the app. You can leave it with a Jet/ACE BE and just port it. Responsiveness is excellent. Frequently it will be better than what you were experiencing on your LAN because everything runs on the same server so there is no network latency. The local PC sends keystrokes to the Citrix server and the Citrix server sends pictures of the app to the local web browser. No data actually transfers unlike it does with a web app or an Azure BE. The access app is installed on the Citrix server and everyone runs it from there. If the app doesn't do any automation of other Office components, you don't even need O365 licenses. You can just use the Access runtime engine. Even though the app runs on Citrix, all users still need their own personal copy of the FE which is easy enough to do. I use a batch file that is very similar to the same batch file i use for LAN users to distribute a fresh copy of the FE each time the app opens.. The FE should NEVER, EVER be shared. The Citrix people won't understand this so make it clear.
Option 2 to convert to AWA isn't a "conversion", it is a rewrite because AWA doesn't support VBA. All your existing code would need to be rewritten as macros and not all features are available which is why AWA died shortly after it was born.
Using an Azure BE is probably not going to work for you. I had an existing app that already was optimized for SQL Server. Converting it to use Azure instead was like watching paint dry. People have reported success with Azure as a BE but only when they ran their own Azure server and it seems like your company just outsourced it's infrastructure so that is a non-starter. With any web BE, response time is at least 100 times slower than the average LAN, possibly worse.
Converting the BE to SQL Server isn't going to help you. See comments above.
Don't let licenses get in the way. Whoever made the decision to move to the cloud had no clue what they were doing and caused this mess. Rewriting the application as a web app will cost thousands upon thousands of dollars. Development time will be at least 5 times what it took to develop the Access app originally.
Since everything is cloud based, Citrix is your simplest solution. This will probably not require any changes to the app. You can leave it with a Jet/ACE BE and just port it. Responsiveness is excellent. Frequently it will be better than what you were experiencing on your LAN because everything runs on the same server so there is no network latency. The local PC sends keystrokes to the Citrix server and the Citrix server sends pictures of the app to the local web browser. No data actually transfers unlike it does with a web app or an Azure BE. The access app is installed on the Citrix server and everyone runs it from there. If the app doesn't do any automation of other Office components, you don't even need O365 licenses. You can just use the Access runtime engine. Even though the app runs on Citrix, all users still need their own personal copy of the FE which is easy enough to do. I use a batch file that is very similar to the same batch file i use for LAN users to distribute a fresh copy of the FE each time the app opens.. The FE should NEVER, EVER be shared. The Citrix people won't understand this so make it clear.
Option 2 to convert to AWA isn't a "conversion", it is a rewrite because AWA doesn't support VBA. All your existing code would need to be rewritten as macros and not all features are available which is why AWA died shortly after it was born.
Using an Azure BE is probably not going to work for you. I had an existing app that already was optimized for SQL Server. Converting it to use Azure instead was like watching paint dry. People have reported success with Azure as a BE but only when they ran their own Azure server and it seems like your company just outsourced it's infrastructure so that is a non-starter. With any web BE, response time is at least 100 times slower than the average LAN, possibly worse.
Converting the BE to SQL Server isn't going to help you. See comments above.
If you want to make some kind of testing i have an early early version of a benchmarking app for Access connecting to remote BE like MsSQL (but not limited..if you have the "correct" odbc driver it should work with anything, my main tests were with MySQL
It uses a listview to populate and show the results...
For now its has being tested and working in Access 2016/2019 x86 on Windows 10 x64 and comes as a compiled Access application
*** Special Note : is compiled.. .accde
Simply hit the button for BenchMark ..other options are not much of use for now
You will presented with the main benchmark form
I reckon is quite easy to work...
you either type/paste the connection string and the SQL (or hit the buttons next to "Quick Connect" and "SQL" and you select from the "History"...you can also save the connections/SQL for reuse)
You select the dataobject (ADO/DAO) and if you want you can change CursorType/LockType and
finally you hit the 1st button on the left for Passthrough Query operation or the 2nd for Linked Table (you can also add your own Linked Table if you write the name of the table and hit the "+" button...remember the connection string should be DAO)
You can get it here : https://www.dropbox.com/sh/1a1c15mmb4gh8y9/AACVgTZYIyH6W6lRH2opsK_Ma?dl=0
P.S. To get everyone starting right away i also "included" a free online hosted MsSQL database ....with one table "Employee" ...hope it doesn't get "destroyed" too soon.
It uses a listview to populate and show the results...
For now its has being tested and working in Access 2016/2019 x86 on Windows 10 x64 and comes as a compiled Access application
*** Special Note : is compiled.. .accde
Simply hit the button for BenchMark ..other options are not much of use for nowYou will presented with the main benchmark form
I reckon is quite easy to work...you either type/paste the connection string and the SQL (or hit the buttons next to "Quick Connect" and "SQL" and you select from the "History"...you can also save the connections/SQL for reuse)
You select the dataobject (ADO/DAO) and if you want you can change CursorType/LockType and
finally you hit the 1st button on the left for Passthrough Query operation or the 2nd for Linked Table (you can also add your own Linked Table if you write the name of the table and hit the "+" button...remember the connection string should be DAO)
You can get it here : https://www.dropbox.com/sh/1a1c15mmb4gh8y9/AACVgTZYIyH6W6lRH2opsK_Ma?dl=0
P.S. To get everyone starting right away i also "included" a free online hosted MsSQL database ....with one table "Employee" ...hope it doesn't get "destroyed" too soon.
I could be wrong, but I believe the RDP "license" included with Windows 10 Pro just means a user can connect to THAT machine remotely. If you need more than that (and it sounds like you would), you'd have to setup a full blown RDS system, like Citrix or a Microsoft RDS system.
Lowest cost solution would be to place the backend on a virtual server. Then link the FE's from the users computer to this new BE
1. This is the simplest.
Set up a hosted Windows Server with Terminal Services, install Access and the frontend for each user, and share the backend.
You will need a CAL (Client Access License) for each user. If you have less than 26 users, the Essential Version could be ideal, though it has some limitations in an existing Domain:
https://docs.microsoft.com/en-us/windows-server-essentials/install/install-and-configure-windows-server-essentials-or-windows-server-essentials-experience
https://www.microsoft.com/en-us/cloud-platform/windows-server-pricing
2. This is a complete rewrite.
3. Hosted SQL Server
This is simply a conversion of the backend. The current Access frontend must still be installed locally.
It can work very well, but be aware that, say, Azure SQL is quite slow for the cheap instances.
4. Hosted SQL Server with new frontend
This is a complete rewrite.
5. This is not a solution.
For 1. and 3. consider using the method described in detail in my article:
Deploy and update a Microsoft Access application with one click
Set up a hosted Windows Server with Terminal Services, install Access and the frontend for each user, and share the backend.
You will need a CAL (Client Access License) for each user. If you have less than 26 users, the Essential Version could be ideal, though it has some limitations in an existing Domain:
https://docs.microsoft.com/en-us/windows-server-essentials/install/install-and-configure-windows-server-essentials-or-windows-server-essentials-experience
https://www.microsoft.com/en-us/cloud-platform/windows-server-pricing
2. This is a complete rewrite.
3. Hosted SQL Server
This is simply a conversion of the backend. The current Access frontend must still be installed locally.
It can work very well, but be aware that, say, Azure SQL is quite slow for the cheap instances.
4. Hosted SQL Server with new frontend
This is a complete rewrite.
5. This is not a solution.
For 1. and 3. consider using the method described in detail in my article:
Deploy and update a Microsoft Access application with one click
ASKER
You guys provided a lot of good information. I have some work to do.
ASKER
Thank you for all your input. I will be pursuing Remote option.
ASKER