Database Size Breakdown (1 Viewer)

[MLUCKHAM]

I have a large database. I want to identify which objects take up the most space. Does anyone know of a way of identifying a list of each object with their relative size?

I can do this in sql server, but no in Access?

 

[gemma-the-husky]

in terms of data, you can work out the size of each record in a table, and multiply it by the number of records to get an approximate size of each table

if the dbs is split, that is sufficient, isn't it?

a code dbs can hardly reach 100Mb.

 

[MLUCKHAM]

I have over a 1,000 objects in this database. It is just over 65mb empty of data. I need to distribute it via the internet and zipped it is now just over 20 mb. Instead of doing a route and branch review of all the objects to check if they are valid and still required, my preference would be to list them in size order. Take the top 10% and review these.

 

[gemma-the-husky]

I have similar big databases, although they zip to 10-12Mb.

I upload them to dropbox, and send or use the dropbox link to transfer the files.

I am not too bothered about the actual dbs size. It is what it is, I think. Maybe a few forms and reports could be dispensed with, but it isn't worth the hassle. I have no idea how you would be able to "size" an individual form or report.

 

[MLUCKHAM]

I think the issue is probably where you have embedded images inside Forms or Reports. 1. These do not compress so well and 2. the size impact is hidden from you. They could have originally been 4 or 5 mb in size when they were embedded. Hey, thanks for your reply as always...

 

[jdraw]

Here is a link to code to determine approximate size of each table in your database.

 

[gemma-the-husky]

Here is a link to code to determine approximate size of each table in your database.

He says it is a large empty code database. I think he is trying to establish the "size" of the individual queries, forms, reports and modules.

 

[jdraw]

Dave,

I agree. I've seen similar requests over the years, but Access doesn't have stats on this and I'm not sure what you would do with the info if it was available. Some streamlining I suppose, but how much streamlining could be expected.

As an option, the OP could add a bit of audit type code to see which modules/queries/forms are used most over some time period; then examine some of those in detail as he suggested.
What exactly is in the database? if it is a question of not knowing if some things are still used, then some investigation of that seems warranted. No need to keep unused code/objects in your operational database.

 

[namliam]

How about writing some code that does something along the lines of
0)
Note the current size of the DB
1)
Delete an object
2)
Compact the database
3)
Note the size
4)
Determine the difference between the previous version and this
5)
Start at 1 again

Should get you a reliable size per object, offcourse coding would be preferedd given we are likely talking "multiple" queries and forms.