Solving a Database Size Issue

[ES]

I have an Access 2000 database that is 392MB. There are about 1 form and 10
tables with a max of 200K records (most are under 20K) I have compacted the
db and this is as small as it gets. Would cells in the tables still add to
the size of the database based on data type? If so is there a way to look at
the actual size of the objects in the database to figure out where the size
is coming from?

Thanks

 

[Dirk Goldgar]

I have an Access 2000 database that is 392MB. There are about 1 form
and 10 tables with a max of 200K records (most are under 20K) I have
compacted the db and this is as small as it gets. Would cells in
the tables still add to the size of the database based on data type?
If so is there a way to look at the actual size of the objects in the
database to figure out where the size is coming from?

Unfortunately, there's no good way to find out how much space each of
the objects is taking in the database file. About the only thing you
can do is create a blank database file, compact it and note the
compacted size, then import an object from the original database,
compact it and note the compacted size, delete that object and import
another, compact and note the compacted size, and so on for each object.
By subtracting the compacted size for the empty database from the
compacted size for each object, you can get an idea of how much space
the objects are taking.

Are you storing any images in your database, either as form/report
backgrounds or in OLE Object fields? Images are stored very
inefficiently in current versions of Access (A2007 is supposed to have
fixed this, IIRC). In all recent versions, an image stored in a
compressed format such as .jpg will also be stored in a bitmap format
for display purposes. This can make your database balloon rapidly. If
that's your problem, the most common solution is to store the images
outside the database, as normal files, and store only the path to the
each image in a text field in the database.

 

[Jerry Whittle]

Tables don't have cells. Spreadsheets do. Off my high horse now.

If you have a field with the Text datatype set to 255 and only put 5
characters in it, it only uses the space needed for the 5 characters. Access
does not waste space.

If you are storing graphic fields in Access, that can cause a lot of bloat.
Better to store the graphics as files external to the database and hyperlink
to them.

Actually 392mb doesn't sound all that large if you have 10 tables with with
between 20K and 200K records in them. Having said that, one of the biggest
wasters of space in a relational database is repeating data due to a lack of
normalization in the design.

 

[ES]

No graphics of any kind.

I was given this db and I am just starting to clean it up. There are a lot
of blank cells in the records and I was wondering if Access allocates the
space based on data type even if the cell is blank. I am not far enough
along in this to know if I can change/correct the field types but if Access
does allocate the space for blank cells then I will look into correcting the
data types where I can. I do not want to make the changes if it is not going
to fix size issue.

I know it is not that large yet but there are two other dbs that I will be
adding into this one. Basically trying to make one db that is more portable
between users (single user db and the person in charge of it changes often) I
can not just set the three dbs on a server to solve the problem as there is
not one available.


Thanks again

 

[ES]

Tables don't have cells. Spreadsheets do. Off my high horse now.

Touché -> my finance / spreadsheet background showing

Thanks for the help

 

[Dirk Goldgar]

No graphics of any kind.

I was given this db and I am just starting to clean it up. There are
a lot of blank cells in the records and I was wondering if Access
allocates the space based on data type even if the cell is blank. I
am not far enough along in this to know if I can change/correct the
field types but if Access does allocate the space for blank cells
then I will look into correcting the data types where I can. I do
not want to make the changes if it is not going to fix size issue.

Some space is going to be allocated for every field in each record,
whether the field is populated or not. However, as Jerry Whittle
pointed out, space isn't wasted in text fields for capacity that is not
actually used. It's normal to have some empty fields in records, and
you shouldn't be concerned about that in itself. If you find that the
majority of fields in a table are never populated, you might consider
breaking that table up into two related tables, so that one table
contains those fields that are almost always populated, while the other
only stores a matching record when there's data to put into one of the
rarely-populated fields. However, this is not commonly needed for
properly normalized tables.

If your tables are not normalized, though, you can often realize
substantial savings by normalizing. For example, if you have a
spreadsheet-like table with fields like ...

RecordID
RecordDescription
Incident1
Incident2
Incident3
Incident4
Incident5
Incident6
Incident7
Incident8
Incident9
Incident10

.... you will like get substantial savings by breaking such a table up
into two related tables, like this ...

Records
-------------
RecordID
RecordDescription

RecordIncidents
---------------------
RecordID
IncidentNumber
IncidentInfo

.... with one record in RecordIncidents for each *populated* Incident
field in the original.

Aside from those general suggestions, I don't think we know enough about
what you're dealing with to know whether you should make any changes to
save space.

 

[Jerry Whittle]

Don't worry about the blank records or even things like a Text field set to
255 but only using a small part of that. Access does not waste space in such
instances.

 

[Tony Toews]

No graphics of any kind.

But are there memo fields of any type with embedded data in them?

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

 

[aaron.kempf]

Jerry

again; maybe if you weren't stuck in the 1st grade-- maybe you could
offer LIGHT, STRONG AND CHEAP.

I can offer all 3 with Access Data Projects.

MDB is for lamers and retards.

-Aaron

 

[Albert D. Kallal]

Try the database splitter on it...

It will split out the forms + code + user interface stuff, and then create a
DATA ONLY FILE.

Do a compact and report on both the resulting files...how big now?

(do this on a copy if you don't want to have a split application, but then
again, it might be time to split it anyway)