MDB grows on every compile/save

[RzB]

There are lots of posts in the archives about growing dbs.
But I have not found any that relate to compiling.

I have found that by just compiling and saving an
application it grows every time I compile. The list
below shows the size after successive compiles...
No changes made other than adding/deleting a blank line.
The is right after a Compact/Repair.

16.49
17.12
17.77
18.41
19.04
19.68
20.32
20.99
22.05
23.10
24.24

Is this normal behavior?

The application is a frontend/backend db Access 2002.
I have also tried this on Access 2003 and I get the same
result, however the starting point is much lower.
Access 2003 does a much better job of compact/repair.
7MB as against 14MB.

Have I got a corrupt db ? It runs just fine for the users.

Thanks,
Roy

 

[TomU]

I can't comment on the actual numbers you have recorded but the growth you're
seeing is typical.

The fact that Compress and Repair in Access 2003 results in a smaller file
than Access 2002 is also typical. I'm not certain but I believe that Access
2003 does a decompile along with compression and Access 2002 just does a
compress. But you can perform a decompile using Access 2002 from the Windows
command line to get the same effect.

 

[RzB]

Tom,

Many thanks for your response. So that probably explains why after a
mornings work I get an unexpected closdown with no warning and I find the
file size is hundreds of megs... Hmmm..

There seem to be other things during development that also increase the file
size unreasonably... Looks like sloppy programming to me - someone ain't
cleaning up properly

Ho hum - as longs as it "normal" and not just me...

Thanks,
Roy

 

[TomU]

I've experienced similar Access shutdowns during longish development sessions
on largish applications to the point where I had to Compress and Repair very
frequently to avoid them. Doing a decompile improved conditions considerably.

To do a decompile, enter something like the following in the Windows command
line (including the quotation marks):
"C:\Program Files"\Microsoft Office\office10\msaccess.exe" /decompile
"C:\MyPath\MyDatabase.mdb"

After the decompile, do compress and repair twice.

 

[TomU]

Another thing to watch... when testing the app and VBA breaks on some error
in the code, avoid making code changes while you're in the break state,
Instead, reset VBA and then make code changes. For reasons I don't
understand, VBA apparantly saves a large amount of unnecessary stuff because
of changes made during the break sate.

 

[RzB]

Tom,

Many thanks for your response. Very good advice. I had noticed some time ago
that code changes during the break state were not a good idea for all kinds
of reasons. That's not unresonable.

However it would seem to me that successive compiles, with nothing more than
a blank line added and removed, should not increase the application size by
half a meg. That does seem unreasonable. In my experience over the years in
software development (since 1969) that's called a bug

Perhaps I'm being harsh

Thanks,
Roy

 

[Albert D.Kallal]

Yes, the database grows each time, since the forms, code models etc. are
them selves stored in a database.

When you save, a copy of made. And, of course we all know that databases
don't recover deleted records.

In fact, for the last 20+ years, the old dbase was the same in that deleted
records are NOT recovered until you do a pack in dbaseiii.

Since code, and all kinds of things are copied during development, then the
parts left behind, and deleted do NOT get space recover.

Come to think of this, a lot of databases work this way...

So, during the day, I often do a compact and repair. In fact, you can set
your database to do this automatically for you when you exit....

 

[RzB]

Albert,

Many thanks for your response.

I still do not think this can possibly be reasonable behaviour.

You do understand that I am making NO changes other than
adding and deleting a blank line - then recompiling.
This causes the db grows by half a meg. HALF A MEG -
think about that. That's a huge amount of space... That
just can't be right. There must be some code somewhere
that isn't tidying up properly...

Thanks,
Roy