How do I reduce the size of an mdb?

[OhioSteve]

I may need to transmit an mdb file over an old-fashioned modem connection. Consequently, creating a small mdb is important. What takes up most of the space in an mdb? I already know about compact/repair. Even after I do that, the mdb is still about 700 kb.

 

[SgtJarrow]

If space is a premium as it used to be in the old days, you need to consider your setup very carefully. Some of the things I would consider

Normalization is extremely important.

Primary keys should be numeric in nature, as they take up less space as foreign keys when needed.

Limit all text fields to the maximum length you expect. If you leave them at the default 50, Access allocates space for the maximum length.

Use the smallest field type you can...Don't use double if a single will do. Don't use integer if a bit (Yes/No) will work.

DO NOT use memo fields.

Test, test, test. Setup the database, add one record and all its daughter data if necessary and see how much is added to the db.

Be prepared to have to go back and adjust.

Those are just some things off the top of my head. Hope this helps a bit. Good Luck.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PHV]

Have you tried to zip the file before transmit ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

I will be moving blank databases to my users. So it MAY not matter how I setup data types. Unless Access somehow "reserves" space for tables based upon their data types....

PHV, you are asking a great question. These are actually .mde files. Can I safely zip them? In the old days people complained about zip programs scrambling data. But perhaps now that is not such a problem.

 

[WildHare]

Limit all text fields to the maximum length you expect. If you leave them at the default 50, Access allocates space for the maximum length.

I had always heard that this was not the case, that Jet does not pad with trailing spaces for text fields - if you store 2 characters in an 200-character text field, only 2 bytes are stored.

Seeing as how these are all empty db's anyway, its probably a moot point. Zipping the files as PHV suggested is probably the best solution in this situation.



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[Ed2020]

Note that Zipping the files will only work if the file is not encrypted (if I remember correctly).

Ed Metcalfe.

Please do not feed the trolls.....

 

[formerTexan]

One more suggestion.

All of the normal design activities in creating an .mde create some bloat as objects are created and deleted and this isn't entirely rectified by compacting. So a good post-design step is to create a new database and import everything from the working copy. The results range from nominal to substantial size reductions.

Cheers,
Bill