Access XP database increasing file

  • Thread starter Thread starter bart6285
  • Start date Start date
B

bart6285

HI,

I want to know the possible cause of increasing file. I've read som
article and some questions and some answers about members in thi
forum. but i'm not totally satisfy, what do i have to check else ?

What i learn now, is to use the compact database tool or tick the us
of compact in options menu. I use this already and it doesn't work.
My database file has a length about 10 megs, i find it scupious becaus
i have not a lot of object in it. To give me an idea about th
problem, i've try to zip my database in WinZip, and Winzip compress i
about 2 Megs.

In second, i've read about to check the recordset and querydef use. W
have to check if recordset or querydef objects are free to memory afte
there use. I check this too... I need your help to see if they are an
other cause to resolve this problem.


Thanks for your Help, i really appreciate it

Bar
 
It is not unusual for any database file, Access or something else, to compress by about
4:1 in a zip file. There are some things that can make the database larger than it needs
to be (even after a compact), although 10MB is a very small Access database.

Some things I remember from some old classes, it may be that not all of them apply to
Access.

1) Field sizes larger than they need to be. If you have a text field with a maximum size
of 50, then there is space made in the file for 50 characters, whether you use them all or
not. This "dead space" is much of the reason database files compress so well.

2) Embedded graphics (pictures on buttons, form backgrounds, etc)

3) Old compiled code that got "lost". Sometimes a decompile or creating a new, blank
database and copying the objects from the old database into it will cause the file size to
shrink.

4) If the file is an mdb file, there are 2 copies of your code. The text code you wrote
and the compiled code.

5) Left over data in temporary tables. If you clear your temporary tables before using
them, but not after using them, then the data is still sitting there until you use the
table again.
 
Wayne Morgan said:
Some things I remember from some old classes, it may be that not all of them apply to
Access.

1) Field sizes larger than they need to be. If you have a text field with a maximum size
of 50, then there is space made in the file for 50 characters, whether you use them all or
not. This "dead space" is much of the reason database files compress so well.
Click to expand...

Doesn't apply to Access.
2) Embedded graphics (pictures on buttons, form backgrounds, etc)
Click to expand...

Yup, or corporate logos on every form.
3) Old compiled code that got "lost". Sometimes a decompile or creating a new, blank
database and copying the objects from the old database into it will cause the file size to
shrink.
Click to expand...

Ayup, although I found that this was only 10% or 20% of a FE after a
month of programming without doing a decompile.
5) Left over data in temporary tables. If you clear your temporary tables before using
them, but not after using them, then the data is still sitting there until you use the
table again.
Click to expand...

True but the extra room required for the temporary tables will still
be consumed and can only be retreived by doing a compact.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
You got some good answers here. I will add a few more things.

The compact in ms-access is NOT THE SAME IDEA as compression. Compression is
a computer technology where the given a amount of data can take less space
by applying some special algorithms to reduce the space required.

Compacting in ms-access is a process where old junk, and un-unused space is
returned. A good number of computer database programs (going back for the
last 20 years) have always included a command to recover this working space
(the command in the old dbase/FoxPro was actually called pack). Thus, if you
delete a bunch of records in a table, the disk space is NOT recovered until
you do a compact. So, compacting has nothing to do with compression.

Since most database systems have this problem, then you as a designer of an
application armed with knowledge will thus WORK VERY HARD TO AVOID the use
to temp tables in your designs. In fact, if you have to do a lot of
processing, and for some reason YOU MUST use temp data tables, then you
should move those tables out into a temporary mdb file,a and thus delete
this file when done each time. However, often the best work around is to
improve your designs.

The references to not closing recordsets also applies. That means when for
all code that uses reocrdsets, when done, you go:

rstYourRecordSet.Close
set rstYourRecordSet = Nothing.

In addition, a number of bloating/file growth problems are fixed with the
JET updates, and again as a matter of professionalism, you no doubt have
applied both the office updates, and for sure the updates to the JET
database engine.
 
Thanks you for your helping Wayne, Tony & Albert,

I follow what you said, by the way i can shrink my database from 10 M
to 4MB.

The major problem was with the format picture than i used. I used ti
format, i chnage the format to jpeg and it reduce approximatively abou
5,5 my db, the other 0,5 mb was about to correct the length field i
all table to eliminate the space in the different field
 
Back
Top