Excessive database size

[Guest]

I keep information about my CD library on a fairly simple database on access.
There is a CD table and a Source info table with names, addresses,
hyperlinks for my most common suppliers. There are a couple of very simple
reports, two or three simple queries and a data entry form for the CD info.
I enter the supplier info directly into the table view.
There are 560 records on the CD table and about 10 on the source table.
When I export the table to a fixed format text file, it creates a file of
about 500KB. The .mdb file itself was 20MB until I compressed it. It is now
"only" 10MB. What in the world is taking up so much space?

 

[Ken Snell \(MVP\)]

All of the information about
-- what the data type of the fields in the table are
-- what all the other properties of the fields in the table are
-- what the indices of the table are
-- the properties and structure of the form
-- the SQL statements of the queries
-- the format of the database file
-- the metadata needed for the entire file to be usable in ACCESS
-- allocated space for data not yet entered (think of as an empty shelf
awaiting new records)
and so on....

The text output that you get is just that -- the data in text format. This
takes very little disk space.

 

[Granny Spitz via AccessMonster.com]

When I export the table to a fixed format text file, it creates a file of
about 500KB. The .mdb file itself was 20MB until I compressed it. It is now
"only" 10MB. What in the world is taking up so much space?

Do you have any graphics or OLE objects stored in these tables? Do you have
embedded graphics in the forms or reports? These can take up considerable
space. Did you used to have more columns in your tables, and then delete
those columns? Even if you compact the DB , the data remains in the table.
This can take up considerable space.

If you're not storing any space hogs, you can run some tests. Create a new
DB file. Import the objects from the original DB file and compact it. Is it
about the same size as the original DB file, or is it much smaller? If it's
much smaller, you had some bloat that you've now eliminated.

If it's about the same size, delete the new DB file, and create another new
one. It should be between 64 and 96 KB, depending on which Access version
you're using. Record this file size, since it's your *base* overhead. Now
import your fixed format text file for the CD table into this file, add the
same indexes on the table as the original CD table, and compact it. How big
is the file now? Subtract the base overhead, and you'll have the size the CD
table should be. Record this. Delete this table and compact the file.
Import the CD table from your original DB and compact it. How big is the
file now? Subtract the base overhead. Is this table way bigger than the
imported text file table?

If so, you've found the bloat. If not, run through the rest of the objects.
Keep comparing the size of the original when imported into the new DB with
the size of the text file output imported into the new DB.

 

[Guest]

My oh yes! I followed your advice and tracked it down to a background
picture I had decided to use on one of the forms a few years ago (it looked
cool). This was a jpg I took on my digital camera, and it's actual size is
about 700KB. Sure enough, as I copied the form into the copy of the CD
database, it suddenly went up to 20MB again! I compacted the new db, and it
again came down to 9.9MB. Now I went in and changed the picture from
Embedded to Linked, and compacted the db again. It is now a healthy 345KB.
What on earth is happening here? Does Access convert the jpg to a bitmap
when a picture is embedded?

 

[Guest]

Does Access convert the jpg to a bitmap when a picture is embedded?

Yes, this happens in Access 2003 and all prior versions. Access 2007 has
finally solved this bloat problem:

http://blogs.msdn.com/access/archive/2005/11/07/490113.aspx

includes the quote:

Modern image support - use png, gif, jpg, etc. without database bloat and
maintain transparency.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________