Best approach to convert Access 2003 ADP project to a cloud-based solution? (1 Viewer)

Utopia65

New member
Local time
Today, 11:29
Joined
Feb 5, 2018
Messages
10

Hi everyone,


I’m looking for guidance on the easiest way to convert an existing Access 2003 ADP (Access Data Project) to a modern cloud-based solution. The application connects to a SQL Server backend and includes several forms, reports, and VBA-based logic.

Here’s what I’m trying to achieve:

  1. Migrate the backend to a cloud-hosted SQL Server (e.g., Azure SQL or Amazon RDS).
  2. Modernize or rebuild the forms for use in a web-based or cloud-native environment.
  3. Retain as much of the existing functionality as possible without an extensive rewrite.
I’ve considered the following options:

  • Rebuilding the forms using Microsoft PowerApps and connecting them to the SQL database.
  • Converting the ADP to ACCDB, using ODBC for SQL Server connectivity, and gradually transitioning.
  • Recreating the application using a modern web framework (e.g., React, Angular) for a full migration.
However, I’m wondering if there are any tools or strategies that could make this process simpler, particularly for the form conversion or retaining Access functionality.

Has anyone here undertaken a similar migration? Any advice, best practices, or pitfalls to avoid?

Thanks in advance for your insights!
 
Retain as much of the existing functionality as possible without an extensive rewrite.
That is what we call an oxymoron.

There are no tools to convert Access apps to anything else. You're on your own. The data is easy to port using SSMA but everything else is a total rebuild. So, I would take this opportunity to redesign and fix any lingering problems.

You also might want to think long and hard about why you need to convert. What is wrong with continuing to maintain the Access app? The shortest path might actually be to convert the ADP to a normal Access app. That will allow you to convert the BE to a modern RDBMS and that might be sufficient. This is actually the only path that lets you save any work at all but it depends on how into the ADP you went.

The ADP was a huge disappointment to me so I never adopted it. Once I realized that it couldn't even link to Jet tables, it was clear that it was an abomination. So, I always just linked my BE's to various RDBMS's over the years and let Access be Access. I wasn't locked into a specific version of SQL Server. I just used ODBC and linked to Oracle, DB2, Sybase, whatever the client wanted.
 
Last edited:
We have done this for a very large set of applications for a client, migrated it to Azure SQL and re-written the front end to use a modern version and look of Access.
It was not a quick process, although no individual part of it was difficult, just quite laborious and required a lot of testing, due to the size of it.

As the data is already in SQL server, there won't be much to do there, it's just the front end conversion that's required. If it's a relatively small system it shouldn't be too onerous.

As Pat has stated there no magic bullet for this unfortunately.
 

Hi everyone,


I’m looking for guidance on the easiest way to convert an existing Access 2003 ADP (Access Data Project) to a modern cloud-based solution. The application connects to a SQL Server backend and includes several forms, reports, and VBA-based logic.

Here’s what I’m trying to achieve:

  1. Migrate the backend to a cloud-hosted SQL Server (e.g., Azure SQL or Amazon RDS).
  2. Modernize or rebuild the forms for use in a web-based or cloud-native environment.
  3. Retain as much of the existing functionality as possible without an extensive rewrite.
I’ve considered the following options:

  • Rebuilding the forms using Microsoft PowerApps and connecting them to the SQL database.
  • Converting the ADP to ACCDB, using ODBC for SQL Server connectivity, and gradually transitioning.
  • Recreating the application using a modern web framework (e.g., React, Angular) for a full migration.
However, I’m wondering if there are any tools or strategies that could make this process simpler, particularly for the form conversion or retaining Access functionality.

Has anyone here undertaken a similar migration? Any advice, best practices, or pitfalls to avoid?

Thanks in advance for your insights!
The key phrase here is "... a cloud-based solution..."

Before you go too far in any direction, it's probably a good idea to decide what "cloud-based solution" actually means and why you might want to implement one.

You mention both Access and PowerApps. They are complementary in one sense, but they fill quite different roles. You need to decide what the ultimate application will need to do as a "cloud-based solution" before picking the development environment. In fact, if there is truly a need for some functions of your application to be available to some remote users at locations not connected to your network, a hybrid application might make sense.
 
Hello and thanks for your thoughts!

You also might want to think long and hard about why you need to convert. What is wrong with continuing to maintain the Access app?
This was developed with Access 2003 32 bit and Gridex 2000 OCX.... none are supported anymore. It might happen that on a future Windows update, the app could stop working!

Is there anyone here interested to develop a new front end and migrating the database to Azure SQL?
Since you're all Access developers, it would be a plus...
 
Please expand. You asked if anyone is interested in developing a new front end an migrating the database to Azure. Are you actually planning to hire a developer to assist your organization?

You haven't yet addressed the question about why this application needs to be cloud-based, though. What problem would that solve for your organization?
 
Please expand. You asked if anyone is interested in developing a new front end an migrating the database to Azure. Are you actually planning to hire a developer to assist your organization?

You haven't yet addressed the question about why this application needs to be cloud-based, though. What problem would that solve for your organization?
Hello George,
I used to be the developer, however I am now focused on other projects and don't have enough time to spend on the migration. Therefore, a decison has to be made: keep the project as is, whit all the risks this entails or migrate.
Cloud based: yes. Since the users who are involved in the data entry process are working remotely, a cloud based solutions is ideal
 
Hello George,
I used to be the developer, however I am now focused on other projects and don't have enough time to spend on the migration. Therefore, a decison has to be made: keep the project as is, whit all the risks this entails or migrate.
Cloud based: yes. Since the users who are involved in the data entry process are working remotely, a cloud based solutions is ideal
So you are planning to hire a consultant to replace yourself?

"...working remotely...." is the key. Thanks. It can be surprising what justifications are often offered for "moving to the cloud", when all that means is the organization wants to move to the latest and greatest whatever.

I think, given the proviso that data entry is the component of the database which must be done remotely, you can look for a solution that retains the existing PC-based interface and moves only the data to a remotely hosted database like Azure SQL. There are many ways to make that happen, of course.

When you interview potential developers, one of the discussions needs to be around pinning down exactly what part of the solution needs to include and how it will be managed. Remote data entry could, for example, be done through a simplified Access FE that supports only the data entry forms. Data management, analysis and reporting, on the other hand, could be done through a more sophisticated Access FE inside the organization.

Then there are considerations about how best to provide connections to remote data entry staff. If you move to a cloud-based database, latency and internet performance issues will rise to the top of the priority list. If you move to a solution involving remote desktop connections into your network, other performance issues rise.

All in all, the conversion from ADP to accdb may not be, in itself, the most crucial factor in achieving a reliable, performative solution.
 
Well we want to avoid service interruption, and the only idea that comes into my mind is to convert/migrate the app in an other environment/platform. Users are currently accessing the client through AnyDesk, which is bad. Network connectivity interruption and database disconnections are very frequent
 
At least that method is only disconnection from the FE, not the FE from the BE?
 
At least that method is only disconnection from the FE, not the FE from the BE?
Not sure what you meant exactly, but the database connection is often lost by the client, requiring Windows Server 2019 to be restarted. Also, Anydesk has downtimes too
 
Please notice that the applications is quite small and only two users are accessing it.
 
Well we want to avoid service interruption, and the only idea that comes into my mind is to convert/migrate the app in an other environment/platform. Users are currently accessing the client through AnyDesk, which is bad. Network connectivity interruption and database disconnections are very freque
Not sure what you meant exactly, but the database connection is often lost by the client, requiring Windows Server 2019 to be restarted. Also, Anydesk has downtimes to
Thank you for clarifying. Obviously, connectivity is an issue in your environment.
Please notice that the applications is quite small and only two users are accessing it.
Two users. One remote? Both remote?
 
I’m looking for guidance on the easiest way to convert an existing Access 2003 ADP (Access Data Project) to a modern cloud-based solution.
As this is an MS Access forum, you will get mostly responses based on MS Access. I would like to suggest moving onto an opensource database, such as MariaDB. That may or may not be something that you may want to consider.

Please notice that the applications is quite small and only two users are accessing it.
Based on the above, I will assume that your "cloud-based solution" may be local (as in a LAN) in nature and that you may not have to use an ISP. Given that, you can set-up one of your PCs (on the LAN) as a server, using Apache. That server will be available to all PCs on your LAN (assuming that is your configuration). Your internet browser would then provide you with the "cloud-based solution". Please note that this is a highly simplified explanation as you will also need to use other resources such as (but not limited to) PHP and HTML.

A benefit of using an opensource database is that it would be "free" and that you would not have to pay Microsoft a licensing fee.
 
Not sure what you meant exactly, but the database connection is often lost by the client, requiring Windows Server 2019 to be restarted. Also, Anydesk has downtimes too
Anydesk is just a remote app to a PC. I use it to support my sisters 200+ miles away.
If I open an app, be it Chrome, Excel, Access, or word and get disconnected, the connection between the app and the data remains, just that I lose the remote connection. When I connect back in, all the windows will be there where they were, when I got disconnected.
 
As this is an MS Access forum, you will get mostly responses based on MS Access. I would like to suggest moving onto an opensource database, such as MariaDB. That may or may not be something that you may want to consider.


Based on the above, I will assume that your "cloud-based solution" may be local (as in a LAN) in nature and that you may not have to use an ISP. Given that, you can set-up one of your PCs (on the LAN) as a server, using Apache. That server will be available to all PCs on your LAN (assuming that is your configuration). Your internet browser would then provide you with the "cloud-based solution". Please note that this is a highly simplified explanation as you will also need to use other resources such as (but not limited to) PHP and HTML.

A benefit of using an opensource database is that it would be "free" and that you would not have to pay Microsoft a licensing fee.
Keep in mind that SQL Server Express is free. It has limitations (e.g. 10GB database size as compared to 2GB for an accdb, but not limited in many other databases).

I'm not sure that investing in a total rewrite from ADP to a browser-based application solves the main problem, which is a remote user (one of two? Both?) one needs to enter data remotely.

We need to know what "remotely" means as well.
Some other pertinent questions include "How small is small?" Rather than a relative term, it would be good to get an objective statement, as theDBGuy asked. How many records are there in a "small" database?
 
To all:
  • Two users, both remote
  • Used to be in LAN, clients running on WIndows 10, server Windows Server 2019 + SQL Server Express
  • This is best forum section to ask for advise. Developers must know the Access/ADP logics and migrate logics behind forms (maybe using ChatGPT, haha) to the new cloud solution
  • Largest table has 33k records and 105k childs
 
To all:
  • Two users, both remote
  • Used to be in LAN, clients running on WIndows 10, server Windows Server 2019 + SQL Server Express
  • This is best forum section to ask for advise. Developers must know the Access/ADP logics and migrate logics behind forms (maybe using ChatGPT, haha) to the new cloud solution
  • Largest table has 33k records and 105k childs
Thanks for the additional information. I am working with someone using ADP now. I'm just helping them maintain it while they are also working on the plans to migrate it to a cloud-based system, mainly using C#. Compared to their system, which is Enterprise wide, I imagine your situation is a lot simpler and perhaps, as already suggested, simply moving the data to either Azure SQL or a more recent version of SQL Server and converting the ADP to a regular ACCDB would be more appropriate. If you are interested, I could probably help with that. If so, please feel free to send me an email using the email address in my signature below.
 

Users who are viewing this thread

Back
Top Bottom