[X]My Assistant

[Marto]

Hi

Is there any way to determine the size of each object in a database .i.e. forms, reports, tables, queries

I have been working on a database recently and the front end size has more than doubled in size to 70mb
I have been changing many things forms, queries and cannot identify which is causing the problem. I have done nothing (as far as I know) that would cause this.

I had some module in the past that could go thru the tables and determine the size. Is there something similar for tables, forms etc?

TIA
Martin

[DanielPineault]

Firstly, have you compacted your db?
Secondly, normally bloating is cause by images.

As for determining the source of bloating the only way is to export individual object to individual databases and then you can get a relative size for each.

I created a tool a while back http://cardaconsultants.com/ms-access-database-analyzer/ but it is commercial.

[GroverParkGeorge]

"...more than doubled in size to 70mb"

If it helps to put this into perspective, 70mb is not a particularly large accdb for a front end.

[Jeff B.]

If you're looking for a tool that provides sizing information for each object, you may need to purchase one.

On the other hand, if you're just curious and/or strapped for cash, you could create a new (empty) db and import one object, check the size, delete the object and import the next, check the size, rinse & repeat... Remember that the Access db "container" will take up a certain amount of space, but that would/should be the same regardless of the object. You might want to run the C & R after deleting each object.

[akn_39]

Hello Everyone: I have a MS Access front-end which also grows over time, not to the extent that Marto describes, but enough to be annoying. In trying to find a solution, I came across this post, and I would be interested in opinions on what is proposed i.e. setting the front-end to Read Only. I tested it briefly and the front-end size did not increase, and I couldn't find any database functions that didn't work as a result of this setting, but I have not had a chance to test it more thoroughly.

Is this a known technique and are there considerations/potential problems with doing this?

Cheers. . .

Tony N.

[GroverParkGeorge]

You want to make the accdb for the Front End Read Only?

That's an interesting idea, but not one I've every tried. What's the rationale for doing that?

[akn_39]

Hi George: The post I was referring to states:

Growing Front-End? Too stupid to be true but it works. My database is used (through the cloud) by several companies and therefore the application can hardly ever be closed for compression (The last one to leave puts the lights out: compresses the database). My customers need to be online in the database all of the time. In less than one week the front-end used to grow from 16Mb to over 2Gb! This was scaring the [censored] out of me. Solution: In the file explorer, simply right-click the front-end database, click 'properties' and check the 'read-only'-box. Access will try to write the enlarged front-end but does not crash on the read-only flag. Again: just too simple to be true! Best regards, Jaap Schokker, miniPLEX B.V., Wageningen, Holland

I was curious if any UtterAccess users have heard of this or tried it, and if there would any undesirable side effects.

Regards. . .

Tony N

[DanielPineault]

Bloating occurs for a reason, and if your db bloats as described "16Mb to over 2Gb", then you need to examine why your db is bloating and fix the underlying issue. Band aid fixes are not proper longer term production solutions! There is a reason the file is supposed to be full read/write!

A small bit a growth is perfectly normal, so say your compacted db if 50MB and it were to grow to 70MB, I wouldn't be worried in the least. Now if that same database grew from 50MB to 200MB, then yes, I'd be figuring out why this was happening and addressing the underlying root cause. In most cases this has to do with either repetitive Insertions/Deletions and/or embedding attachments within a database.

[Marto]

I did the individual import of the items in the database and identified a report with and embedded image.
I removed it and db now back at 24mb.

Thanks to all for the advice

[GroverParkGeorge]

Thanks, that's probably an uncommon approach to the accdb bloat problem.

For one thing, the back end with your data would normally be stored in a shared location on a LAN. And that means you can't make it read-only. After all, people must write to it to add new records.

For another thing, bloat is most commonly the result of the processes Daniel describes. And that would be limited to the copy of the front end accdb which you give to each user. Each user, therefore, would be bloating only their own copy of the FE, whichyou can address by setting the option to compact the accdb every time the user closes it.

 COmpactOnClose.jpg ( 25.31K )Number of downloads: 0


In the quote you posted, the implication is that that person has ignored the standard advice NOT to share a single FE accdb among multiple users. An option that is not popular among experienced developers.

That means, as far as I can see, that under normal circumstances no one really benefits from making an accdb read-only. That said, if you want to implement it in a production environment, I would be quite curious to learn how it works out.

[isladogs]

Coming back to the original question, almost all of the space occupied by a database is due to the tables ….unless you have lots of embedded images in your forms/reports.
Obviously attachment fields will be particularly bad in terms of space taken up
In contrast, queries occupy almost no space.

If you want to get an idea of the space filled by each non system table, you may find this utility by vkl interesting Table Size Analysis
It uses a similar idea to that suggested by Jeff B but instead copies each table into a new database in turn, measuring the increase in size after each table is added.
When done the external database is deleted. Its fast and seems to work well.

[FrankRuperto]

I have seen frontend bloating after making several mods to objects like forms and reports, so I usually compact after making mods.
Since my frontend makes use of temp tables, which can also bloat, when launching my app I always use a shortcut that runs a batch file that makes a copy of the frontend master and then launches the frontend copy. This way my users are always running a fresh frontend with no bloating or corruption. This also makes it easier to distribute updates, and makes sure everyone is working with the same version.