Migrating an Access 2002 Application Front-end to Access 365 or SQL Server.

[Jayeff]

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. This is a temporary option (6 months to 18 months) because the director is thinking of changing to a brand new solution.

I have already done the test to transfer forms, tables, queries and modules to Access 365 but I have run into 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.

I would like to know if there is a gain to migrate from Access 2002 front-end and VBA to the Access 2019 front-End VBA 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 Access 2002 VBA application and code uses the DAO method?

Maybe, I should keep the software under respirator by eliminating data history in large tables. 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?

[#2]

@Jayeff Well we give a lot of material to discuss ....

Lets try to clear some...

At first what kind of errors you get ...some screenshots and some more info would be most helpful.

If you are migrating from Access 2002 to a 64bit version then you will encounter severe issues due to changes in the .dlls declaration and lack of many ActiveX controls.

Now as for the actual question ...why to migrate to a newer version..... the answer would be that you want to be on a newer version...while the only significant change at first glance is the Ribbon and some graphics effects...it seems that under the hood there are some small changes that allow for better functionality.

Last but not least....your mention about problems,corruption ...etc. means that you have issues...what/why it needs investigation...but i can tell you that for sure...just switching to SQL server for BE ...it isn't going to resolve problems...it would rather add some new interesting ones...SQL server is a total different story than Access...unless you treat/handle it the way is meant to be treated/handled it will far more issues...not to mention being slower in many cases ...BUT if you "switch" to SQL philosophy the performance would be stellar.

You mentioned about .mde....this is not "migration" ...you just using the old application via the newer Access...you must "migrate" everything to the newer format (.accdb/.accde)