Database Object Size

[Kiwiman]

I have a database that is 29mb in size. THe biggest table contains only 288 records and 20 fields, so is not excessive. There are no embedded pictures or files of any sort. I compact the db after close but the size does not get any smaller.

I need to find out the size of each database object so I can review the setup and vba code for any inefficiencies.

Any help would be appreciated

 

[genomon]

The database size is 29Mb. That is as small as it will compact to. I don't understand "size of each database object". For this tiny of a database you can just use Tools|Analyze|Performance and run performance analyzer.

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[traingamer]

You could create a new database and import the objects one at a time to compare relative sizes.
(or try compact and repair to see if that makes any difference).


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MichaelRed]

you can get/generate an approximation of the 'space' requirements of the TABLE type recordsets. MS has some additional details, but most of the requirements are simply the sum of field lengths of the fields times the number of records. additional space is required -in a simlar computation- for each index included. then there is some small overhead just for the deffinition of the tables.

you can find the mininmum size of your flavor of database simply by creating an empty / blank db. this figure does vary considerably with the version / release, so can be important for small MS Access applications.

the size of queries is some multiple of the length of the sql property of the query, however this pales in comparision to the database size of their contribution, as the size may expand dramatically dependin on the instantation of the queries,

I am not aware of any 'computation' or information on the 'size' of any other individual objects.



MichaelRed

 

[TheAceMan1]

How are ya Kiwiman . . .

[blue]traingamer[/blue] is on the right track (unless you wanna do the math and thats alot of work).

Only difference is I'd first [blue]import everything into a new DB[/blue] and note the difference. If that doesn't help then import tables one by one looking for that big jump . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Kiwiman]

Hi Guys

Thanks for all your input - it is much appreciated. Further information. This is a small database, as there is currently very little information in it (at design stage), but it will grow.

While comparing two back ups I performed (5 days apart) the db had grown from 5.5mb to 22mb - with no real increase in the data stored. I have also now split the db (the back end is only 2 mb), so all the size is in the other objects.

I'll try importing into a blank db to see if I can see the difference.

Regards

 

[MichaelRed]

the growth is common in databases. in Ms. Access, the growth is much more noticable than others.

most (all?) relational databases create (use) internal spaces when ever recordsets are created e.g. run queries. this space is generally made avaialble through simple expansion of the database footprint (size). It is seldom reused or recoverd, except when the database is ,for Ms. A. "Compacted and Repaired", or more generally "rebuilt". the 'industrial strength databases *SQL Server, Oracle, et,) generally include ONLY the database (Tables, stored Procedures, etc.) while Ms. A, includes the FE elements (Forms, Reports, etc.) as well as the procedures (Functions, Subs, Macros). While I am not aware of any published articles, it seems that Ms. A. treats theses additional elements in a sdimilar manner - simply making additional space available to the 'application through expansion -w/o consideration to (permanant) removal of old versions of these objects - until the "Compact and Repair" facillity is run. Thus every time you modify an object or run a query there is a good chance that the database will grow.




MichaelRed