We help IT Professionals succeed at work.

MS Access, SQL, Virtual Server

Alex C
Alex C used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
The choices are
1. RDP...a powerful RDS server...users connect to the Remote Server FE+BE hosted on the same cloud network ...the speed is great...the con is the license and that you are still using Access
2. SQL...(BE)a solution that holds the future...it requires quite some work.. rewriting quite a lot if you choose the path of directly talking to SQL...it can be easier if you choose to link the tables ( some experts recommend it 110%)
The choice is yours... connection needs to be at least adequate

Author

Commented:
Thank you. Much appreciate your input.
Distinguished Expert 2017

Commented:
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.
John TsioumprisSoftware & Systems Engineer

Commented:
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
1510260.jpgSimply hit the button for BenchMark ..other options are not much of use for now
You will presented with the main benchmark form
1510261.jpgI 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.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Author

Commented:
You guys provided a lot of good information. I have some work to do.

Author

Commented:
Thank you for all your input. I will be pursuing Remote option.