How to find size in KB of Access objects?

[ReTheOff]

Hi all, I appreciate any help you may have.

Is there a way to determine the size of all the objects in my MDB? Size in KB, bytes, whatever. I just would like to know which items are taking up the most space in my MDB.

I have Access XP with an SQL Server backend. I have at least 2000 objects in my 115MB MDB file, in which I distribute to each client. The majority of the objects are linked tables and queries. But there are many modules, local tables, and forms too. So many objects make it difficult to find and manage them.

My objective here is to reduce the size of my MDB and make the client more efficient. I have tried the compact and repair, which reduces the size by only a few MB. I tried a /decompile switch on the mdb, and that only made the file bigger! I am not sure what to do next. I know there are many objects that are not being used now, that have not been removed over years of development. If I could only see which ones are the largest, I would start with them, and see if they are needed.

Any help is appreciated! Thanks!

 

[psemianonymous]

I don't know the direct answer to your question, if there is indeed an answer, but I know:

1. Embedded images are huge. This goes for forms and reports. Change them to 'linked', especially if you're using the same image for multiple reports/forms. One image can use well over 1MB of space.

2. Queries and table links and modules will be reasonably small. Most of your space is used by forms and reports.


See Tony Toews' (Access MVP) page about database frontend bloat--it's actually ridiculous how many useful pages he has (I've answered more questions here recently WITH a link than without):

http://www.granite.ab.ca/access/bloatfe.htm

- among other things, it has a bunch of MSKB article links.

 

[ReTheOff]

Well that page was interesting. Thanks!

Some of the suggestions will be very difficult for me to figure out simply because I have so darn many objects. Now, I am taking over this system, and I did not develop at least 95% of it originally. I know pretty well which of my objects may have some of these issues, but not on all the others I didnt create.

On that page you sent me to, there were some KB art's and on one KB was another link I found very interesting.

810415 - Access 2002 Format Database Bloat Is Not Stopped by Compacting

Over the last six months, this has happend a lot. I am using 2002 format. Can I revert back to 2000?

About the images, we have very little images. Many, many reports, but without images. Forms too, have very little if any images.

It would still be very helpful to see the size of my objects. With thousands of objects, it would narrow down my search tremedously.

Thanks!

 

[ReTheOff]

Holy Cow!

I just converted back to 2000 file format. Then I ran the msaccess.exe c:\myfile.mdb /decompile. After that I opened the mdb while holding shift to bypass the startup, and then hit ctl+g to open the vbe. While in the vbe I ran "Debug/Compile" to compile the code again. Then I closed the mdb, reopened it with the Shift key again, and then ran "Tools/compact and repair" to compact the mdb.

At this point, when I closed my file, it was only 69MB. Cool! After the conversion back to 2000 format it was a whopping 216MB! After decompile and compact, 69MB. BUT ONLY IN 2000 FILE FORMAT!

What doesnt that work in 2002 format?

Oh well, I would still like to know if anyone has code to find the size of all the objects in the mdb.