pruning a database

[Eric]

What are the things I can do to make a database smaller
in their order of most significant impact. My 2000 mdb is
now 46Mb and the mde is almost as big. When I zip a file
it's about 9Mb. I use compact and repair regularly. This
database size does not represent any significant amount
data. What takes up the most space?...code, forms,
reports, tables, fields with excess space allocation

thanks

 

[x]

whats your table structure?

 

[Allen Browne]

Remove any non-text objects from the database. That includes OLE Object
fields (use Hyperlinks instead), and graphics on your forms or reports.
Access is *very* inefficient in the way it stores graphical objects.

The other things are relatively minor, such as:
- Make sure that the Name AutoCorrect boxes are off (Tools | Options
General).
- Set the Unicode Compression property of text fields to Yes (lower pane in
table design).
- Reuse objects to keep the count down, e.g. use OpenReport with a
WhereCondition that covers several cases rather than saving several reports.

 

['69 Camaro]

Hi, Eric.

it's about 9Mb. I use compact and repair regularly. This
database size does not represent any significant amount
data. What takes up the most space?

If it's not data taking significant space, then look for graphics embedded
on forms or reports. Each graphic image on a form is about 32 KB added to
the database size.

If it's not the graphics taking up the space, then make a copy of the
database. Use the /decompile command-line switch on the database copy to
decompile the code. Open the copy and compile the code in the VB Editor.
Now, compact and repair. Is the copied database about the same size as the
original? If the copy is significantly smaller than the original, then your
database is the victim of code bloat, and you've just solved the problem.

Unicode compression may also help save space in the tables that hold text or
memo data, so make sure that Unicode compression is turned on.

What takes up the most space?..., fields with excess space allocation

There's no such thing as "excess space" allocation for records in a Jet
database. One of the compromises of DBMS's is space versus speed of access.
It's much faster for the database engine to access data if all records in a
table are exactly the same size. However, this wastes significant disk
space, because each record using less than the allotted text space will fill
the excess space with space characters. The designers of Jet decided to
forego the speed of access advantage and went with the disk space advantage
for the desktop database. No extra space characters are added to records to
make them all of equal size in each table. If a record only uses five
characters for a field size of 20, then only five characters are stored, not
20.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[John Vinson]

What are the things I can do to make a database smaller
in their order of most significant impact. My 2000 mdb is
now 46Mb and the mde is almost as big. When I zip a file
it's about 9Mb. I use compact and repair regularly. This
database size does not represent any significant amount
data. What takes up the most space?...code, forms,
reports, tables, fields with excess space allocation

thanks

Just a note to add to Camaro's and Allen's suggestions: "fields with
excess space allocation" are not to blame, since Access does not store
trailing blanks. "XYZ" takes up three bytes (plus or minus UniCode
compression issues), whether the field is Text(3) or Text(255).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tom Wickerath]

In addition to the other good suggestions you have received, here are some more things to check:

Database Bloat KB articles
http://tinyurl.com/2dmpw


Tom
_________________________________________


What are the things I can do to make a database smaller
in their order of most significant impact. My 2000 mdb is
now 46Mb and the mde is almost as big. When I zip a file
it's about 9Mb. I use compact and repair regularly. This
database size does not represent any significant amount
data. What takes up the most space?...code, forms,
reports, tables, fields with excess space allocation

thanks

 

[Eric]

Thanks everybody...great poop.