Design mode for Macros extremely slow after migration to Access 2019

[Nicontma]

Hello,



I recently migrated a large Access application from Access 2007 32Bit to Access 2019 64Bit. This took some months of work but in the end I got everything to work in the new version.

Now my problem is that we have ~250 Macros in the database. In Access 2007 I could easily open the design view in ~1 second. In the new version macros now need sometimes multiple minutes to open the design view. I was also able to replicate this with the 365 version of Access. The cause seems to be the number of Macros as when exporting a single macro into an empty Access DB I can open the design view nearly instantaneously, but as soon as I import all other macros it will again take multiple minutes to open a single macro in design view.

Has anyone already encountered such an issue or has an idea what could be the problem? I would appreciate any help I could get, as working in the Access 2019 is currently near impossible for me.

[Bulzie]

Not sure on that issue with macros, i have had reports with very complex record sources(queries calling other queries) take a long time to open in design i 2007. I used to have to copy the recordsource SQL query to a text file, delete it from the report to be able to open and edit the report itself.

That seems like alot of macros, is it possible to recreate those with some vba code and looping? Or you could convert each macro to VBA (is a wizard for that) but not sure if that would resolve the issue and would take alot of time as I think you would need to do them 1 at a time.

Is your database linked to BE? Maybe make the tables local to test and see if that does anything. I know access has limits on number of objects but I looked alittle and could not find if macros had a number limit.

[Micron]

Unless you have a need for the BigInt data type, I'd say one mistake was installing 64 bit instead of 32 bit version. With that many macros, I suspect your db is approaching the max size limit, which is 2GB. I say that because if you have that many, you probably have a lot of other forms/queries/reports that are basically the same in design where the only difference is the data they use or show. You could try opening the db in a 32 bit version to be sure that it is still ok (not corrupted). If ok, they maybe copy the db and see what happens if you use the tool to convert the macros to code. I'm assuming this can still be done in the version you have. Then delete the macros and see if all is ok. Those macros will be far from perfect, but at least you could reduce the db size. Also, did you try a compact repair on a copy to see if that improves anything?

[Welshgasman]

I have upgraded to 2019, but 32bit.
If you want to upload a db with just a few, or all of the macros, I can see what it does with me.
I am also on win 11.

[CJ_London]

Just a guess but it may be every time you open the macro in design view a 32-64bit conversion is taking place.

to test, open one of your slow opening macros, then save it and close. Does this improve the situation when you next open?

a ‘deeper’ test would be to open, make a small change then save and then close

[Nicontma]

> I suspect your db is approaching the max size limit, which is 2GB

The Access DB currently sits at ~400Mb, so that should not be a problem

> Also, did you try a compact repair on a copy to see if that improves anything?

I did, but that had no impact

> You could try opening the db in a 32 bit version

Just tried that in a fresh 32 bit Access install, but also did not see any improvements.

> Just a guess but it may be every time you open the macro in design view a 32-64bit conversion is taking place.

This also does not seem to change anything.

> see what happens if you use the tool to convert the macros to code

Just tried that and it would need many more hours of manual cleanup as some larger operations in macros are divided into multiple smaller submacros in the same file. i.e. I will have a macro "Invoice.startCheck" that in itself runs "Invoice.validateSum" etc.
Using the autoconversion to code that gets replaced with DoCmd.RunMacro "Invoice.validateSum" so I would have to manually fix all such situations.

--

Could it be that the newer Access versions just have a terrible performance when opening large macros? I just exported one of the biggest macros into a new empty access db and opening the design view still needs nearly a whole minute to load, opposed to ~3 seconds on Access 2007.

[Bulzie]

I just exported one of the biggest macros into a new empty access db and opening the design view still needs nearly a whole minute to load, opposed to ~3 seconds on Access 2007.

This does seem odd, would think it is not trying to check logic, joins, etc., should just be open the macro logic in design view. On the bigger macros, do you have alot of calls to run other queries or macros that run queries? Maybe take one of those bigger ones and start deleted lines to see if you can pintpoint the issue. Maybe one of your lines has the issue and cannot resolve in the macro.

I vote to keep MS Access 2007

[Minty]

Is the database split?
If it is 400MB is pretty sizeable for a front end on it's own.