What contributes to mdb file size?

[BlueWolverine]

Hello,
MS Access 2003 on XP PRO.

I have noticed that my file size has gotten huge on my document-generating
mdb file.

It started as a 12 MB excel file that contained, oh a bajillion lines of
code and formulas to generate documents based on a central list.

Then the Access version pumped up to 67MB, then 108MB, then I did the
performance analyzer, added some indexes, and the thing is 187MB now. The
main table has maybe, MAYBE 40 fields and MAYBE 1000 records.

What is causing this thing to get so big? And can I shrink it?

Thanks.

 

[BlueWolverine]

I have a few pictures on buttons, but they are standard clipart that you can
assign from Properties. I don't know what DAO code is such that I could tell
you if I am running it.

Also, What is compacting the db?

 

[Roger Carlson]

Adding and deleting a lot of data is the most common reason for the database
to grow. When records or tables are deleted, the size of the database does
not decrease until you Compact the database.

Tools>Database Utilities>Compact and Repair Database


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[BlueWolverine]

LMFAO. That took the file size down to friggin 2MB.

Thanks so much.

 

[BlueWolverine]

It appears to me that everytime I run queries, it saves that data, such that
if I run every query in my db, it'll be back up to FULL SIZE of 188MB. Is
there a way to COMPACT on CLOSE?

 

[Jerry Whittle]

Compact on Close is an option, but don't do it. It's possible that it will
corrupt the database. I've had it happen. It's better to manually compact and
repair AFTER doing a full backup of the database first.

Running queries can cause bloat. Access requires some internal room to do
such things as sorting and grouping.

If your queries do things like creating or appending records to temporary or
'working' tables, that can cause bloat. Also inserting and deleting records
in you main tables.

Speaking of which, if you have 40 fields in one table, it's very likely that
your data isn't properly normalized into a proper table structure. For
example if you have something like a Sales table and had the Buyer's full
name, address, city, state, zip, phone, etc., on each record, that would take
up a lot of space. The Buyer's information should be in a Buyers table with
just one field linking the Sales and Buyer information.

BUT 108 MB is only about 1/20th of the max size of an Access database which
is 2 GB. If the database grows to 108 MB each time you run it, leave it
alone. No use beating your head against the wall. Now if it keeps on growing,
to say over 1 GB, you need to worry about it then.

 

[BlueWolverine]

I don't have anything as easy to deal with a buyer's table. Unfortunately,
most of the information stored in the table with 40 fields is specific and
unique to each record. I have a couple folding queries that I work out of.
I would consider ditching the big table and switching to folding tables.
(instead of check boxes for M, T, W, Th,F, etc have one field for day of the
week and seven records...) but the rework for everything would be pretty bad.
As it is, the db is way faster than excel file i was working in originally,
so I like it.

Is there a specific way to back up a DB or do you just mean "save another
copy somewhere on your disk?"

Thank you though!

 

[BlueWolverine]

One other question,
When the file bloats because of queries and such, does that make it run
faster, because the queries are already largely calculated, or does it make
it run slower because it takes up more memory on the computer?

 

[John W. Vinson]

I don't have anything as easy to deal with a buyer's table. Unfortunately,
most of the information stored in the table with 40 fields is specific and
unique to each record. I have a couple folding queries that I work out of.
I would consider ditching the big table and switching to folding tables.
(instead of check boxes for M, T, W, Th,F, etc have one field for day of the
week and seven records...) but the rework for everything would be pretty bad.
As it is, the db is way faster than excel file i was working in originally,
so I like it.

Keep going.

Storing data (a day of the week) in a fieldname is A VERY BAD IDEA and will
make your queries and reports much more complicated than they would be with a
normalized design. You're not using a spreadsheet any more; take advantage of
that fact, and normalize your data! 20 fields is a *very wide table*. It will
be worth the effort in the long run.

Is there a specific way to back up a DB or do you just mean "save another
copy somewhere on your disk?"

Simply use Windows Explorer to make a copy of the .mdb file.

Compacting a database will actually improve performance (some queries may run
a bit slow the first time they are run, but they will be recompiled with the
actual statistics of the current data, possibly improving the query plan).

 

[Fred]

This is Mr. Simple here, but sometimes that can be useful.

Not sure if the other guys noticed what I think that you said. It sounds
like you are saying that just executing your queries one time bloats your
small database from 2 Meg up to 188 Meg. That is NOT normal.

Fred

Adding and deleting a lot of data is the most common reason for the database
to grow. When records or tables are deleted, the size of the database does
not decrease until you Compact the database.

Tools>Database Utilities>Compact and Repair Database


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Fred]

PS: The only time I've seen bloat THAT wild has been due to storing pictures
IN tables, (not the way to store them) where the bloat can be 100 times
larger than the pictures. I'd double check that you are not doing that.