Link to home
Create AccountLog in
Avatar of colinasad
colinasadFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to move a SQL-Server / Access "project" application off a local server and onto the Cloud?

Over the past couple of decades I have developed an Access "project" (ADP) front-end to a SQL-Server database for a client.
They are still running SQL Server 2008 R2, and the Access front-end is still being developed under Access 2007, after Microsoft dropped support for "projects" in more recent Access versions.
At my client's site, the SQL Server database runs on their own server, and each user has an "ADE" copy of the Access front-end on their own PCs, that connect with the SQL Server database.

My client would now like to move to a web-hosted system, allowing their own staff and customers to access their data from pretty much anywhere.

Do we have to start from scratch? This client has been running their business since the late 1970s, and they now have quite a sophisticated application that has grown from a pre-MSDOS platform, through MS-DOS, and into its current Access / SQL-Server incarnation. It would be good if we could somehow make use of the existing SQL Server database and Access work. I'm pretty sure SQL-Server databases can be hosted on the cloud (I connect to my client's database remotely using its IP Address), but is there a suitable web-based front-end that would be compatible with my Access application?

Can anyone point me in the direction of a migration path that might achieve this? If not, to any case studies of anyone else who has achieved this sort of conversion? I can't be the first person faced with this task!

Any suggestions or help gratefully received.
Thanks. Colin.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

There is no web interface for Access. If you want to go all-in on a web-based system you'd have to rewrite from the ground up. There are some products out there which claim to be able to 'webify' your Access database, but they don't really work (and I'm not at all sure they would work with the ADP format).

Azure is one platform that can host SQL Server databases, and if your application is built to true client-server specs, then you can do this. If not, then you'll have a good bit of work to do to make it so. Here's a migration writeup that may help: https://datamigration.microsoft.com/scenario/access-to-azuresqldb?step=1. Again, I'm not sure if this is even relevant with the ADP format.

One of the best ways to handle this is to use a Remote Desktop Service (RDS) setup. If you use RDS, users can remote in from anywhere and work just like they were sitting at their desk in the office. It can be pricey to setup and maintain, but it would require virtually no changes in your Access db. You can do this entirely with Microsoft products, or you can use Citrix, or other 3rd party products like Thinify (https://www.cybelesoft.com/blog/convert-access-web-app/?cn-reloaded=1).
Avatar of colinasad

ASKER

Thanks for the fast response, Scott.
Pretty much as I had feared!
My client has an on-site IT staff member who helps with other, wider IT issues (they have a network of about 20 PCs and various shared devices). He also maintains their existing, separate web-site that receives daily feeds from the main on-site database that I am responsible for. We exchange data both ways, with orders placed on the web-site imported into "my" database via raw "txt" files.
Some of the staff can already connect to their workplace PCs remotely and run our application from home.

However, my client is now looking to put the whole thing on the web so we can offer customers more historical information and have no delays in updating stock availability.

I'll see what other feeback comes in before I describe the enormity of the task to my client.

Best regards. Colin.
Well if the client wants web then there are no shortcuts...you either create an ASP.NET MVC core application or PHP/JS...Probably if you continue on the Ms boat you could start making changes to make the transition easier..
Move as much functionality you can to SQL...Views/SP/Functions...whatever can be handled by SQL
Try to move your Business Logic to external..you could experiment by creating .NET .dlls that are COM visible so they are usable by Access
Last but surely not least .....coding the FE
Do we have to start from scratch?
As the others have said - you can't get there from here.  But there are solutions.  You just need to focus your requirements more narrowly so you know how to approach the solution.

I never used .adp's.  I found them clunky and restrictive so I stayed with the linked tables model which works quite well if you design the application with some understanding of how client/server works.  You could convert to this model but if the .adp's I have tried to work with are any example, this will probably be a huge undertaking and you would be better off starting from scratch so you can clean up as you go.  And once you decide to start from scratch, you can move away from Access entirely if that is what you want.  However, if Access still makes more sense than hiring a web development team and spending at least four times the cost and time to develop something web based rather than Access based.  Access with Jet/ACE or SQL Server or any other RDBMS works very well as a distributed app using Citrix or remote desktop.

However, you may decide you can stay with the .adp for a while longer (you will need to switch sooner rather than later so you should start now with at least the planning) and simply create an inquiry only web based FE that connects to the existing SQL Server tables.  Or, if you don't want to share the actual tables, you can work out a sync routine that pushes changes to the web database several times per day or even interactively.  Due to external threats, many company use mirrored internal/external databases to avoid outside contamination of their inside the wall databases.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks to the various exports who responded.
You all seemed to agree and confirmed what I had feared; there is no easy/secure/possible way to quickly adapt my existing Access ADP / SQL Server system to a "public-access" web-site.
I will pass this news and some of your other specific suggestions on to my client.
Many thanks and best regards.
Colin.
You never told us exactly why you needed web access to the ENTIRE application.  One of the solutions we never discussed was Citrix.  This works fine as long as you are only dealing with known users.  It cannot work for anonymous users.  If it is just inquires you need to support, you can make them via email.  Access can read the email folder and respond.  This will require specific formatting of the email so that you can process the request but it isn't actually very difficult.

As long as the ENTIRE application must be public, there is no Access solution.
Hello, Pat.
Thanks for your further comments.
My client wants the entire system to be web-hosted so that the staff can work on the system from places other than the company's offices. Staff include agents and sales people who are located in several different countries. They also want their customers to be able to see how their orders are progressing and "live" stock availability when orders are being placed by customers.
From the late 1970s to the late 1990s my client was exclusively a wholesaler with only a few hundred "live" customers at any time. Since the late 1990s they have also started selling individual pieces to "joe public" on their web-site too. Different types of customers would be allowed to perform different tasks and see different amounts of data, but the staff would need to be able to carry out all their existing tasks.
As long as the ENTIRE application must be public, there is no Access solution
This is not entirely true....the client could distribute an Access application along with Runtime while the ALL-Web solution is developed..By having a SQL BE the users could have various levels of "access"
"Public" does not mean on-line, it means that you don't know who is accessing your application ahead of time.  Some web pages require registration but many do not.  They are simply "public".  Anyone can go there and do whatever the app allows.
Thanks for the further comments.
My client already has a web-site which someone else looks after and I'm not 100% sure what levels of access vistors are currently given.
Certainly some aspects of the web-site must be "public" so that completely new, potential customers can visit it as a "guest" and look at stock items to discover what prices and stock levels are available. When a visitor decides they wish to place an order there is some degree of registration so that address and credit card details can be stored before an order can be lodged.
My client wants my "office-based" system to be made available on-line, so that all visitors can have real-time stock levels available to them and so that repeat customers can look at their orders history and get a better idea of how large orders are being prepared.
They basically want their whole, currently office-based, system to become web-based, with some truly "public" access for new visitors to be able to "drop-in" to see what's available for sale.
There is a difference between making the existing application available to all (which requires a rewrite as a web app) and simply keeping the stock levels updated on the website (requires an interface between your app and the database used by the website).  Please try to separate these concepts.   You have already accepted a response but I'm not sure you have clarity in your own mind what your direction should be.

I'm not pushing you to keep the adp.  I personally never liked .adp's and MS has sunset them so there is no further development so if you want to continue with Access, you are looking at rewriting the Access app anyway.  So, unless you can separate the app into what needs to be public and  what will remain private, you won't be able to use Access and will need to rebuild on some other platform.