COmpacting database in 2002 vs. 2000

[Guest]

I have a very large database that I've developed in Access 2000. When I
compact it it is around 80MB. It will grow as I work on it, and then always
compacts to somewhere around 80MB when compacted again. I put this DB on a
system with Access 2002 and it compacts to less than 10MB! Is this normal, or
is there a problem somewhere? Was there a change made in 2002 where access
uses a lot less space? It still seems to function normally, and I can still
view the code and it all looks OK, but I'm concerned that there may be a
problem.

 

[Allen Browne]

Access 2002 does not compact better than A2000.
In fact, an Access 2002/3 format file compacts less well than an A2000
format file (regardless of version.)

Your experience suggests that the database was carrying some serious bloat -
possibly spurious binary code. You may be able to fix this by a decompile,
even in Access 2000.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thanks for the reply. How do I decompile?

Access 2002 does not compact better than A2000.
In fact, an Access 2002/3 format file compacts less well than an A2000
format file (regardless of version.)

Your experience suggests that the database was carrying some serious bloat -
possibly spurious binary code. You may be able to fix this by a decompile,
even in Access 2000.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Guest]

I found an old message in this site that had your decompile instructions. I
took one of my copies of my DB on my laptop that has Access 2000 and
decompiled it, then compacted it, and it went from 95MB to 6.5MB! SOunds like
I may need to just do an occasional decompile while still using 2000. ACcess
2002 must somehow automatically do the decompile for you if it detects that
it needs it?? The compacted version is a bit bigger in 2002, but still MUCH
smaller than the versions I was getting in 2000. Thanks again.

 

[Allen Browne]

An MDB contains 2 versions of the code:
- the text version (what you view and edit);
- the compiled version (the binary that actually executes.)

The compiled version is different in every version of Access. Each version
supports different features. Later versions can execute the binary from
earlier versions. But any change to the code means it has to compile again
in the new version. Now if you open this MDB in an earlier version again, it
can't use the later binary at all, and so it compiles the whole thing back
into the old version again.

All of that is supposed to occur transparently. It doesn't work 100%. You
can finish up with the old binary (or parts of it) left in the file. A
compact alone doesn't get rid of these errors. There are other things that
can cause this problem as well, such as leaving the Compile On Demand option
set (Tools | Options from the code window), editing in break mode, and other
corruptions that result in the text and compiled versions being out of sync
(various symptoms such as phantom break points, or old code still executing
after you removed it.)

A decompile instucts Access to discard the binary. When you open the
database again, it will then create the binary form the text version, and so
the 2 versions are back in sync again.

My practice is to decompile as soon as there is any clue that something's
going wrong. While developing, I'll probably decompile once a day anyway.
Once development is completed, you create an MDE (which contains only the
compiled version (not the Text version), and so cannot decompile) which
never needs decompiling.

Always create a backup before decompiling: occasionly it is the text version
that has gone bad, so when you discard the binary, you can't compile again.
At least with a bad text version, you may be able to rescue some of your
code from the gobblygook visible in the module.

So, the sequence is:
1. Backup (in case something goes wrong.)
2. Compact (to dump any deleted modules.)
3. Decompile (to dump the compiled code.)
4. Compact (to completely remove the binary.)
5. Compile (to create the binary again.)

If you have any startup code, hold down Shift during each restart, so the
code doesn't automatically get recompiled before you are ready.

Hope that explains why switching version had the effect it did, and how to
manage this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message