I've read opinions across the internet that say if you design or MS Access FrontEnd properly, it shouldn't shrink too much when you do a compact. I've got one front end I'm using that is typically around 15 MB when compacted, but grows to 20-25 MB while I'm working on it! Is this something I should be concerned about?
+2
A:
As you are adding reports and so on, I do not think you should be concerned. I suggest that you decompile* fairly regularly when you are working on code, forms and reports.
Remou
2010-07-26 13:57:07
I tried decompiling through a shortcut using "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "\\Users\Database\DB.mdb" /decompile before but it seemed to just open the database and the size of it didn't change at all which I found strange. So I'm not sure that way worked. I'll try the method at lessthandot later in the day and see what happens.
Jeff
2010-07-26 14:07:36
Remou
2010-07-26 14:18:18
Ok, I just wasn't sure if it was unhealthy for a front end to grow 7-10 MB while working on it. Thanks!
Jeff
2010-07-26 14:23:55
Make sure you keep regular back-ups - even a quick copy can save a lot of grief if a form suddenly becomes unusable. It can be an idea to copy to a fresh MDB if you make a lot of changes, because, for example, the number of controls allowed for the life of a form includes deleted controls.
Remou
2010-07-26 14:31:17
I do keep backups which have saved me in the past. I had no idea there was a limit to the number of controls on a form! I have created a fresh MDB and copied everything over, but this will definitely persuade me to do that more often!
Jeff
2010-07-26 15:32:13
+3
A:
There is a distinction between development and production use.
during development, bloat should be expected -- you're churning the data pages in your front end, revising forms, reports, modules, etc., so there will be frequent discarding of data pages. There is nothing wrong with this. During development, you should compact regularly, and occasionally decompile (not often -- I tend to do it maybe once a day during heavy development, and/or immediately before distributing a new front end into production use).
during production use, a properly-designed front end should not bloat much. Yes, when you supply a compiled and compacted front end, it will grow some during use, but after a while, that growth should top off. But you shouldn't be concerned about that, as front ends are fungible. If something goes wrong with one, you just replace it with a new one.
The most common reason people encounter bloat in front ends is because they design them incorrectly, including temporary data in their front end (e.g., a table that has data appended to it that is then deleted). Temp data belongs in a temp file. All of my apps have a tmp.mdb that is distributed along with the front end and stored in the same folder as the front end, and all temporary data is stored there. I generally never bother to compact temp files.
Other sources of bloat might include:
design changes to forms/reports made in code (which would be the same in terms of bloat as the human developer making the same changes). This is almost always a design error, in my opinion.
changes to saved QueryDefs in the app. This one is less significant, as the amount of bloat is quite small compared to other types of bloat. However, if this is being done thousands of times in a session, it could theoretically reach the level of significance. There are a few good reasons to edit saved QueryDefs at runtime, but not very many, so while I wouldn't call doing this a design error, it would be a red flag that it needed to be checked to make sure it's not something that can be accomplished efficiently without editing the saved QueryDef.
David-W-Fenton
2010-07-26 18:32:37