Minimizing front end growth

[Guest]

Does anyone have any tips on minimizing front end growth in fe/be splits? In
specific, I noticed these:

#1: While under development, my db's tend to grow, even with regular compile
& compact/repair. In one example, it had grown to about 12 MB. When I created
a blank DB and imported all the objects, it was under 4 MB. Now after a
couple more weeks of dev, it is back to almost 6 MB.

#2: I have a routine that uses the Windows API to allow the user to browse
for the back end DB, then refreshes all the links (so that the BE can be at
different paths for different users; in one major implementation, some users
run it across a LAN, while others run it locally on a terminal server; both
are sharing the same BE). I just distributed a new FE at about 5,600 K. After
refreshing the links, the FE size jumped to about 7,400 K. Why?

Should I add an automated compact/repair to the FE? If so, what is the best
practice? At each close, or on a daily/weekly basis?

 

[Allen Browne]

Suggestions:

1. Decompile periodically.

2. Be especially careful of modifying the same file with different versions
of Access. Each uses a different binary. Always decompile before switching
(at least before switching backwards.)

3. Make sure the Name AutoCorrupt boxes are unchecked under:
Tools | Options | General

4. Instead of always running the reconnect code, run it only when the path
is invalid (which is easy to test with an OpenRecordset.)

5. IME, A2002 seems to be the worst bloater.

 

[Guest]

Thanks, Allen.

1. How do I decompile? Pardon my ignorance on this one.
2. Using A2003 only, saving in A2002-2003 format; clients running on A2003
runtime.
3. AutoCorrupt turned off - always
4. Reconnect simply tries to do a dlookup when the app opens. I trap for any
error (assuming that the reason for failure would be no link to the BE, and
so far, that assumption has been accurate 100% of the time) and take the user
to the Browse window to locate the BE. So far, this has happened only once
per system per distribution.
5. See #1

 

[Guest]

Just a followup:

I found some documentation that renaming the db decompiles the code, so I
went through the Project Explorer, renamed it, recompiled, then renamed it
back to the original name, recompiled, compacted/repaired, and now it is 500k
larger than before I renamed it the first time!

 

[Allen Browne]

No, renaming causes a loss of compilation, but that's not the same as a
decompile.

An MDB contains 2 copies of the code:
- the text version (what you see and edit), and
- the machine version (compiled code that actually executes.)

It is quite common for these 2 to get out of sync. Symptoms include
bloating, break points that don't break (or phantom break points), and
errors that don't make sense.

Decompiling discards the compiled version of the code. Access will
automatically generate the compiled version again out of the text, so then
the sync-errors are gone, and the legacy bloat is dumped.

To decompile, make a backup first, and then enter something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

Then compact to get rid of the junk.

Recreating the file will generally get you a smaller result anyway. But it
is certainly worth decompiling immediately before recreating. Otherwise you
can be importing the out-of-sync junk into the new mdb.

If you have a really stubborn form or report that's proving a headache, you
may be able to export it to a text file with:
SaveAsText acForm, "Form1", "C:\Form1.txt"
Then load it into your new database with:
LoadFromText acForm, "Form1", "C:\Form1.txt"

 

[Guest]

Thank you for the extended explanation. It really helps me when I know what
is actually happening instead of just doing it. That dropped the size down to
about 4,700k, which should be about right. This will certainly become part of
my maintenance plan for all my db's.