Migrating an Access 2002 Application to Access 2019 or SQL Server?

by JF Landry   Last Updated August 14, 2019 18:26 PM

I have a series of Access 2002 "Front-end/Back-end" applications all related to each other. For example, application A has linked tables with application B and vice versa.

The applications are at a stage where daily compaction and repair is required due to the volume and high level of corruption. Moreover, to be able to make the applications work properly, I must make the changes in a virtual environment with Access 2002. I also need to reinstall "Access runtime 2010 - 32bits" and copy the Access files (.mde) on every workstation (Windows 10) every time I am making a change in the applications.

@Gustav This is a temporary option (6 months to 18 months) because the customer would like to go to a complete solution with SQL database. The studied solution is configurable and already has a SQL database schema.

I have already done the test to transfer forms, tables, queries and modules to Access 365 but I have errors in the VBA code. All business rules are coded in the VBA code. I also did the transfer of the tables to SQL Server 2017 but I'm afraid I will have to change a lot of VBA code because of the disuse of the DAO engine in the Access 365 front-end.

In fact, to be clearer, I wonder about the need to change the front end to Access knowing that it is a temporary solution.

Maybe, I should keep the software under respirator by eliminating data history in large tables. The time the client takes their decision. Find the "sweet spot" that would allow me to erase and continue to maintain it without having to worry about corruptions because I have a hard time seeing a substantial gain in the migration to the Access 365 front-end. What do you think?

I have already proposed to migrate applications and tables in the new version of to Access 2019 and even moving on SQL Server. However, for now, I must put the application on the respirator and continue the daily compaction until a decision is made.

I would like to know if there is a gain to migrate from Access 2002 to the Access 2019 version knowing the total limit of 2 GB Access. What are the major constraints he would have to migrate to a SQL database knowing that the VBA application and code uses the DAO method?

Answers 1

There may be none. Open the database in Access 2019/365 and save it in the 2007 (accdb) format, and check it out.

As for the distribution, you can make this fully automatic using a script and a shortcut. It is explained in full in my article:

Deploy and update a Microsoft Access application with one click

If you don't have an account, browse to the link: Read the full article.

Push hard to get a confirmation on the move of all the shared tables to an SQL Server backend.

August 14, 2019 13:02 PM

Related Questions

Only Run or Display a query if its contains data

Updated July 30, 2019 07:26 AM

Hiding Subform Column in Ms-Access VBA

Updated July 26, 2018 07:26 AM

how to put bullet point in the form access?

Updated March 28, 2017 08:26 AM