Performance in Microsoft Access is worse after splitting - My personal experience

(Updated 2010-09-27)

[ Main | AccessSplitting ]

aka How to speed up complex forms and reports with many records each with subreports.

There are also many other useful tips on speeding up performance at the Microsoft Access Performance FAQ.  Many, most or almost all of these are quite applicable after splitting an MDB.

When I split a fairly large combined Access 97 MDB which all the users were sharing into a FE/BE in Access 97 I found somewhat poorer performance on most things.  More importantly we very poor performance on complex forms which have lots of combo boxes and/or subforms as well as reports with subreports.

One complex form went from a second or so to about eight or ten seconds. A series of reports which had thousands of lines and about sixty or a hundred pages and usually one subreport per line went from 20 or 30 seconds to 20 or 30 minutes.

On the complex forms I ended up hiding the forms when they exited them rather than closing them. In hindsight I should've left the recordsource of each of the subforms empty until the user clicked on the tab. However this was a bit more programming and, at the time, I was in a hurry. 

The downside of this has happened to me has been that Access 97 has puked on me.  Every open form and subform was corrupted.  Given that the most complex of these forms was open at the time on my system I had to re-import older versions of quite a number of forms.  The logic which hides that form now closes the form when it's an MDB.

On the reports with subreports I ended up putting all the data back on one report and displaying the main report fields only when the data changed. (RPG is much nicer in this aspect. <smile>)  I did not want to depend on the "Hide Duplicates" property of controls as they could legitimately be the same from one master record to another.   What went from 20 to 30 seconds before splitting to 20 or 30 minutes after splitting went back to 20 or 30 seconds.

I suspect the problem here is that MS is not handling the queries on the forms combo boxes or subform record sources and the sub reports as well as they could. But I'm just guessing.

 

[ Splitting | AccessMain ]

Auto FE Updater   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Wrench and gear Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony  Search Contact Tony's Blog Privacy Policy Table of Contents

Website copyright 1995-2013 Tony Toews