Database size up-normaly large?

[christakis]

Hi,

Is 12MB normal for an Access 2003 database with 3 tables (30 fields each but no data in), 3 forms and 2 queries? Something is not right... Any ideas?

Best Regards,
Christakis

 

[boblarson]

12Mb is not that large. But run COMPACT AND REPAIR to see what you get for a size then. Any use of temporary objects or make table queries, pictures embedded or saved in the database can all bloat the database.

 

[christakis]

12Mb is not that large. But run COMPACT AND REPAIR to see what you get for a size then. Any use of temporary objects or make table queries, pictures embedded or saved in the database can all bloat the database.

What can create temporary objects? I have no pictures so thats not a prob. My query is updated very often is there a way to make sure that the results are not saved constantly?

Were is the compact and repair option? Today my pc complained that it didn't have enough memory and 5 min later all my forms were corrupted.

With my calculations the database would be 300mb+ with no data in. That doesnt sound right now does it?

Cheers,
Chris

 

[boblarson]

What can create temporary objects? I have no pictures so thats not a prob. My query is updated very often is there a way to make sure that the results are not saved constantly?

What is the sql for your query and how are you using it?

Were is the compact and repair option?

Access 2003 and prior: Tools > Database Utilities > Compact and Repair

Access 2007: Big Round Office Button > Manage > Compact and Repair

 

[christakis]

What is the sql for your query and how are you using it?

I dont have the exact SQL code now as I left my laptop at my office. After what happened I was too angry to bring work at home. I can post it tomorrow if you want.

A summary for it is:
I have a table with 25 fields. The query reads all those fields. For each field in the query I also have a hidden criterion in the form of
"[Field 1] = [Forms]![Form1]![cboBlahBlah] or [Forms]![Form1]![cboBlahBlah] Is Null"

This filters out the query based on what the user selected (or not) in comboboxes in a form.

The query results are then shown in a subform (under the combo boxes in Form1)

On the Change Event of each combo box I have a Me.Refresh command to refresh the Form and hence the results of the query.

This is for one type of component. I plan to have the same principle for the remaining 23 types of components. Stupid?!

All I want is a split form with combo boxes on the top and a query on the bottom which would filter out and display my assets for each category.

Best Regards,
Christakis

 

[Boro]

Ofcourse it is not. Are You shure that it is that size. Take look at properties on mdb file. I have tested one mdb access file with just one table with record 50 characters long and i fulfilled data base with over 20 milions of records and tahat was max of size

 

[boblarson]

Christakis:

Queries in and of themselves should not bloat anything. Now, deleting and appending data, using make tables, and like I said before - embedding graphics, can all cause bloat. But just a query to display information should not be doing anything to it.

Now, it would be good if we could get your database normalized. It sounds from your description that you have a lot of fields where you should have fewer fields and more rows of data.

 

[christakis]

Thank you both for your reply.

The tables are as normalised as they can be for the specific application. It is a very awkward inventory list. Products are split into 24 categories, the few fields they have in common only take discrete values which are based on standards that have not changed for decades. For this reason I just put all these in one table and treat it as a "value pool" rather than a table. Sounds outrageous but its the best solution.

Regarding the size. Something was definitely wrong. When Access crashed something must have been locked in the database file. Even with Compress and Repair the file didn't fall below 12MB. I started the same database from scratch. Currently I have more stuff that what I had before and the size is 1.1MB. This sounds about right.

Cheers,
Christakis