why does my mdb bloat?

[micks55]

Solved: why does my mdb bloat?

SOLVED - I Think!!!.
Hi all,
This is not about temporary tables or images etc. because there are none so I think it is about what Access is doing (or not doing) behind the scenes.
I constantly Compact and Repair and I always work with a fresh copy when I make major changes, but it seems that I can't avoid a bloated mdb.
I have just deleted every table, form, query, macro, report and module etc so there is nothing in there but after yet another Compact & Repair it remains at over 4464KB.
I know I can start from scratch, import all of the objects and reset the References and this does reduce the size as you would expect. But when I do that, I end up with a different problem. Access seems to forget that all subforms have been saved and it takes ages to close a form where there is a sub. Even if i go to every form, move a control, move it back, close and save and then do the same with all of the sub forms, I still dont seem to get anywhere.
It drives me nuts so does anyone know what's going on? I'm using A2k.
Great forum, loads of good stuff. Thanks.

 

[georgedwilkinson]

That's a pretty small database. Why do you think it's too big?

The way I understand the way Jet works (thanks to Pat Hartman), the Access file size is increased as Jet performs SQL statements (seen and unseen queries). The extra file space is used to perform the over-head that every DBMS needs to do quick requests and make sure that data manipulation statements are consistent.

My guess is that every time you compact, you are undoing some of the work that Jet had to do to gain these enhancements. Thus, after every compact, Jet must re-do some of the work that it did the last time it opened a certain table or query.

I could be wrong. Brent, Leigh, or Pat could probably give a better (more accurate) explanation but I'm not sure us regular people would understand it if they did.

 

[micks55]

Thanks for comments GW and I am sure that your guess is not far off the mark.
The reason that I think it's big is because it's empty. No forms, no, modules, no nothing - all deleted. I just started a new blank database and before I create anything it's 92KB so it seems to me that after deleting everything out of my current project, the compact and repair should bring it down to almost the same small size. It just seemed to me that at 4400KB with nothing in there it must be storing a lot of junk. Also, earlier editions of the same project with plenty of forms, queries etc are below the 3000KB size.
Then there's the second problem where a freshly imported everything seems to not recognise them as saved.
Thanks for being there.

 

[The_Doc_Man]

Problem # 1 is that 92 Kb might not be so bad as you think. There is no such thing as an empty database. You see, there are hidden tables and overhead tables galore in a properly structured database. There are the tabledefs and fielddefs for the intrinsic tables. And there are the collection overhead structures for the collections of tables, queries, documents (=closed reports & forms), forms (open), reports (open), and modules. Every one of those collections has a parent collection management structure, even when empty. I don't know what alignment and block boundary rules exist at that level, but remember that one disk buffer in access is 2k bytes. You also have the overhead of all intrinsic references for the default database, and the overhead of the default user/group collections structure (which is never empty because there are always groups USERS and ADMINS and user ADMIN (no S) no matter what you do.

So, in essence, an empty database isn't empty.

Problem #2 is the size of the database after you empty it and compress it. In theory, compression will build an empty (92K) database and export the entities in the current database to the compressed database. Here is where we get on infirm ground, but I would think that unless you un-hid everying in the queries section, and particularly if you had a LOT of forms that were created via wizard, you might have some hidden queries that served those forms. Ditto for reports.

There is also the issue of whether you had lots of references. The reference management structure isn't cheap.

You must not attempt to edit it, but if you un-hid the system tables, you might find that they had a lot of records that could not be deleted if there were hidden queries that still depended on them. So it all depends on exactly how well you cleaned out everything, remembering that a populated collection, even if populated only with hidden objects, still takes up a lot of space.

Offhand, can't address #3 problem, not recognizing things freshly imported. I'll have to think on that one a while.

 

[micks55]

Hi Doc Man and thanks for taking the time to explain the deeper goings on.

I understand totally that it would never be empty and I wouldn't have a clue how to unhide and look at the system tables. I just don't see how after deleting all of the obvious stuff it would still be 4400KB. If I start a new mdb and import everything it's under 3500KB but then I have to wait a full two minutes before it closes a form after a very minor design change. Also forms take a long while to open.

It seems wrong to me that I have to choose between a fast bloated mdb (9516KB) or a compact (3560KB) but very slow one.

In case it helps, the References are (from the top). VB for Apps, MS Access 9.0 Obj Lib, OLE Auto'n, MS DAO 3.6 Obj Lib, MS Outlook 9.0 Obj Lib.

I have been trying things and I remembered that I read somewhere that there was a limit to the total of items you could put on a form which I think it was "about 700 or so including any that you delete". I've been on this project for months so I wondered if even though I dont have 700, perhaps I could have created and dumped that many. I decided to re-make the problem forms using copy and paste and it has made a difference.

Another probability is bad coding because I am very much an amateur. I don't think that Compiling shows up all code problems and I think I must have some sort of glitch because I have had freeze ups even though every procedure has error trapping.

One poor piece of code that I know about is that I can send an email with attachments via Outlook but I know this will fall over because I don't know how to check that Outlook is loaded on the computer.

I have never grasped debugging and I have failed miserably to understand how to do it so if you know of a dummy's guide anywhere on the internet I would very much appreciate being pointed towards it.

Thanks again for your time. Mike.

 

[gemma-the-husky]

it certainly shouldnt take minutes to save a form change

so....

is this on your desktop machine, or is it on a network (which will slow it down!)

embedded Images (even tiny bmps) definitely cause inordinately large increases in mdb size.

i just posted a couple of dbs's - 1-3 tables, a couple of queries etc - these were only about 120Kb.

but 4Mb sounds as if theres something else going on there

 

[micks55]

Hi Gemma,
Looks like were were posting replys to each others questions at the same time. Spooky...
Yes it's on desktop, No there are no images. It does seem to be an Access house keeping problem.
I hope my reply to your list box problem helps.
Thanks Mike

 

[Pat Hartman]

Try the undocumented /decompile switch. Obviously, you'll need to adjust the path to match your version of Access.

C:\Program Files\Microsoft Office\OFFICE11\Access\OFFICE11\msaccess.exe /decompile

Decompile removes all the compiled code so it will act somewhat the way importing the objects acts.

You can run this from the "run" box or create a desktop shortcut. I use both A2007 and A2003 so I keep two desktop shortcuts.

 

[micks55]

Thanks Pat, I'll give it a try.

 

[micks55]

I didn't understand where to put the file name in the sample line that Pat suggested so I went looking for decompile info and found Allen Browne. I followed his suggestion to the letter...even though he's talking A97 and I am using A2k!.
http://www.allenbrowne.com/ser-48.html
Here's the extract that helped me.
Database is corrupt.
It is quite common to discover that a working Access 97 database is partially corrupt, and will not convert. Decompile:
1.Make a backup copy of your mdb file.
2. While Access is not running, enter something like this at the command prompt.
It is one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\MyPath\MyDatabase.mdb"
Then close this instance of Access.
3. Open the database, and compact: Tools | Database Utilities | Compact.
4. Press Ctrl+G to open the Immediate Window.
From the Debug menu, choose Compile and Save All Modules.
Solve any issues until the mdb does compile.
5. Close Access 97. Open your new version, and try the conversion again.

I ignored no. 5 because I wasn't trying to convert.

After that I started a fresh mdb, turned off Name Autocorrect in Tools/Options/General and closed the clean mdb (I picked that nugget up in this forum).

Then I imported the tables (most are linked but not all) and did a Compact & Repair and closed again (also from this forum, I think tables first is important).

Back in for the last time, imported everything else then a final C&R and close.

The issue I was having was that a 16000kb mdb was compressing down to 12000kb. Or if I started a fresh mdb and did a full import it went to 7750kb but did weird things like not saving forms or throwing me out when I was making changes to forms.

I now have a 6250kb that seems great so fingers crossed.

Many, Many Thanks to all you guys and especially to Pat.
Mike