Database Size gone Mad!

[Guest]

Help! I've created a small database (<500kb) which contains a couple of
tables, imports data (up to 150,000 max) and the main table only contains
<300,000 records. Just data - no pics or videos or links. It also contains
several queries including action qrs and some macros to run them. That's it.
It has grown just using the db to over 128megs! I then compressed and
repaired it using utilities but is still down to a huge 60 megs. Any idea
why? any way to reduce it?

 

[Arvin Meyer [MVP]]

300,000 records at only 200 bytes each would be 60 MB of data, if the format
were plain text and there were no indexes. Remember, with Unicode, each
character of text is 2 bytes. So even100 characters of text in each row
would do that.

 

[Guest]

As newer Access databases can be up to 2 GB, 60 or even 128 MB isn't all that
huge. Heck I'm the DBA on a 6 GB Oracle database and it's considered tiny.
Databases store data so they tend to get big.

But to answer your question.

The #1 way to reduce the size of a database is to normalize the data. If you
have repeating data, say the Name, Address, Phone Number, etc., of a customer
on each order line, this repeating data takes up space.

Next is to NOT use temporary tables for running reports, ect.

Don't over index things. Access has a bad habit of creating indexes that you
don't really need. For example if you make a field named CustID the primary
key of a table, Access creates two indexes for that field! One because it's a
primary key and another just because it has ID in the field name. By default
Access makes indexes on any field that contains the following anywhere in the
field name: ID; key; code; num. Check all your tables for indexed that you
don't need.

One thing I don't readily suggest is deleting or archiving data outside of
the database. Too many times someone has wanted this "old" data after it's
been deleted.

 

[Guest]

Thank you to both of you. I feel better that perhaps it is destined to
remain this size as I'm not repeating many records (though the database is
designed to store some repeated customer data and indicate that they are
repeat customers but this has to be in the same main table). I haven't
over-indexed and I'm not using temp tables for reports but I am importing
weekly data then using a series of action queries to append data, check for
duplicates, indicate new customers and run a couple of reports. Think I will
have to live with the size and presumably run compact and repair every so
often?

sall

 

[Aaron Kempf]

Jerry

I still don't believe that you're an Oracle guy.
If you knew a 'real database' you wouldn't use MDB for anything.

If you knew a 'real database' then you'd be using Access Data Projects