mdb file size ?

[shiner]

What elements really add to the physical size of a mdb file? I am working on a pretty extensive database and the file is already up to 56 mb - and I only have 63 records in there!

When I am done, I expect to have more queries than anything. I consider myself a knowledgeable newbie who has the best intentions, but I find that I take shortcuts rather than find out the best way to do things. Today for instance, I made 60 queries - each looking for if someone had an equipment item checked out or not (true or false), but there are 30 equipment items... So you get query_eq_item_1_true, query_eq_item_1_false, query_eq_item_2_true, and so on...

I know these lazy measures add up to some of the problem, but is there one thing developers should avoid in an attempt to keep the file size to a minimum?

Thanks!

 

[MikeAngelastro]

Normalize your database. Each table should define one thing and only one thing.

 

[shiner]

Ok, so I have a database with equipment items. One table holds the descriptions of each item. So the db sees you pass stuff like equipment_item_1 - but there are multiple elements for that item (size, style, description). I currently really work off of two main tables - the descriptions and everything else. This allows me to see what personnel have checked out each item. At the moment, I dont know of a better way to organize it.

The table "Descriptions" has 30 fields - eq_item_1_desc - eq_item_30_desc

The other table is called "master" and has employee names, adressess... and goes on to include the item number, the size they have, and the style they have. So, say there are 30 non-equipment fields, 30 equipment item fields, 30 style fields, 30 size fields, and 30 misc fields I put in there trying to plan for a future variable about equipment that I had not though of.... So that table is a monster with 150 fields!!!

I think to break it up smaller, I need a quick course in linking tables, and primary key's. I have taken some CBT's on Access, but nothing really explains it well enough for me to get it to work.

Ultimatly, I am currently working on a very simplistic level trying to complete a complex project and this is where the delima lies.

Again ~ all of your help is greatly appreciated!

 

[Oldsoftboss]

I think Pat Hartman needs to butt in here. She is a great one for explaining relational databases, where you set up 5 separate tables to hold the data, then set up relationships and use queries to extract the required data.
Hope she can help
Dave

 

[Autoeng]

Have you compacted the db yet? Do so under Tools. You would be suprised how much it can shrink.

Autoeng

 

[shiner]

Autoeng,
No I have not compacted it yet. I tried to e-mail it home from the office, but that file size was too great. I will have to wait until monday to make these changes. Thanks for the tip!

 

[Pat Hartman]

Take a look at these articles for starters. Then take another stab at designing your tables and post what you come up with.

http://support.microsoft.com/support/kb/articles/Q288/9/49.ASP

 

[gmlwong]

Compact often; daily, especially if you are adding so many objects but one thing from my experience...only develop and add those objects that the database will really need and call on...don't create "dead" or a lot of unused objects that just hang around.

Also, when sending it over email, WINzip it...all ISP's have file attachment limits; most are 10MB, unless you pay to make larger files available...I have a limit of 10MB from my ISP and additional meg's are $3 each...not bad...

go to: <www.winzip.com> for the freeware evaluation version (8.1) of WINzip.

 

[shiner]

thanks for the tip! After compacting it, we went from 50+mb to 7.5mb! That is much better, but still larger than I ever anticipated for a virtually empty db.

 

[boblarson]

Are you using any graphics? Those can cause things to swell quickly too.

 

[Oldsoftboss]

You can select - under tools - options to compact on close, which will do it automatically each time you close the DB
Dave

 

[jadair]

SIze Restriction

Just a little reminder. If you plan on creating a very large database then don't use Access. Access has a 6GB size restriction. This is a very large Db but I would hate to see you put in all of this work to find out that you can't keep adding records.

 

[Cosmos75]

You can select - under tools - options to compact on close, which will do it automatically each time you close the DB
Dave

This option is only available for Access 2000 and later if I am not mistaken.

 

[wizcow2]

Another trick is to split your database. Its in Tools.
This creates another database with just your tables.
If you have a lot of files this can make a difference.
You could also email your front end and back end databases separtately.

Tom

 

[Autoeng]

Re: SIze Restriction

Just a little reminder. If you plan on creating a very large database then don't use Access. Access has a 6GB size restriction. This is a very large Db but I would hate to see you put in all of this work to find out that you can't keep adding records.

The limit of an Access db is 2GB.