Bloating Front End (FE) Microsoft Access MDB/MDEs

(Updated 2010-09-27)

Temporary Tables

Don’t use or create temporary tables in front end. Such as when importing data or speeding up reports. Instead see the The TempTables.MDB illustrates how to use a temporary MDB in your Microsoft Access app Tips page on this topic at my website which has sample code for creating an MDB, creating tables within that MDB, linking to the tables, unlinking and deleting the MDB.

Embedded images such as logos

Are you using a lot of embedded images? Say the company logo on all the forms and reports? If so:
1) create one subreport and one subform which contain the logo and reference those everywhere required
2) for a more flexible system use images kept as files on the server. See the Image Handling Tip page for more details. This will also allow for easier updating in the future should they change their logo.

Custom Toolbars

An undocumented problem is custom toolbars. Along the way the sysObjects table seemed to get quite bogged down over this. Create new custom toolbars in a new container mdb and import all the objects into the new container. One report was the file size dropped from a compacted 55 MB to 16MB. So now the loading time is instantaneous and they have significant resource savings. 

Note that compacting is insufficient to clean up the toolbars.   You must do an import.   This behavior was reported in Access 2000 and thus may be cleaned up in Access 2002 and newer.

Close recordsets

ACC2000: To Help Prevent Database Bloat, Explicitly Close Recordsets - 209847  mentions that "In Microsoft Access, if you use Data Access Objects (DAO) to open a recordset and you do not explicitly close the recordset, DAO may hold onto the memory that it used for its previous compile until the next recordset is opened. ... Because the recordset memory is not released, each time that you loop through code, DAO may recompile, using more memory and increasing the size of the database."

Other MS KB articles on bloating

Note that most articles are applicable to other versions of Access.

ACC2002: To Help Prevent MDB Bloat, Explicitly Close Recordsets - 289562
ACC2002: Running Update Query Causes Database Bloat - 295245
Database bloat is not stopped by compacting database with Access 2002 format - 810415
ACC2000: Database Bloats When Importing Large Text File - 239527 mentions installing MDAC 2.1 SP2 if record level locking used or creating or importing tables directly causes Unicode compression to be set to No.
ACC2000: Manipulating Objects with ADO May Cause Database Bloat - 199005
ACC2000: Manipulating Objects with DAO May Cause Database Bloat - 197953
ACC2000: DB Size Different When DB Is Converted to Access 2000 - 208285
ACC2000: Delete Queries Cause Size of Replicated Database to Grow - 207629
ACC2000: To Help Prevent Database Bloat, Explicitly Close Recordsets - 209847
ACC2000: Using Lightweight Objects in Access - 208196 but I'm not at all sure that the techniques mention in this article are very practical as they won't save you a lot of space.
ACC97: Manipulating Objects with DAO May Cause Database Bloat - 172285
ACC97: Failed Append Query Causes Bloat in Multi-User Database - 174341

During development

Access 2002 Format Database Bloat Is Not Stopped by Compacting - 810415 (2003-08-24)

Finally

Slight bloating of the FE, say 10% or 20% after the first few days and very slowly thereafter, can't be helped as that's just how things work with Access.   Or so has been my experience.   This is probably caused by Access updating the query statistics and plans for queries the first time the user runs a query in the new MDE.

[ Access Tips | Access | Main ]

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

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