Design changes = Huge MDB file - Why? 1

[Blackshark]

Hi all,

I have a database (A2K) that when I edit a report a number of times in design mode can cause the MDB file to jump from a compacted 10Meg to a whopping 200Meg!! I have had it at gigs before. I have it set to compact on close and after a v.v.v.v.v. long time, it closes, compacted back down to 10Meg.

I have tried repairing it. I have tried a new DB and importing everything.

I dotn mind at home, the Promethia P4 I have monsters through it. However at work its a nightmare (30+ minutes to compact and close).

Any thoughts?

Thanks

Tim

 

[jdegeorge]

I have this happen all the time. Every time I've ever looked this up I get no explanation. I just keep on compacting. I even have a routine in my MDB that automatically compacts on closing if the MDB is greater than 10MB.

Jim DeGeorge

 

[cghoga]

Tim,

Try splitting the database into a front-end (GUI --> forms, reports, etc.) and a back-end (tables).


hope this helps.

 

[Blackshark]

Ahh, excellent suggestion. I suspect I'll still have the same issue with the front end as it seems to only take hold once I have imported the forms. If I import everything but them, I can edit my reports till the 'large white and black animals' come home (thats cows BTW).

Thanks

TIm

 

[cmmrfrds]

You could be dynamically creating objects and not releasing the objects when done. What kind of objects are you creating in vba code? For example, recordsets, forms, controls? Are you dynamically editing forms? Is it only when you edit the report? Do you also run the report? Can you isolate each process and determine when the size grows?

 

[Blackshark]

I am using quite a few different objects (Word, Excel, Outlook, PDF) within my VB code. However the growth only occurs while I edit and re-edit the reports. Make a change, go to preview, go back to design and make some more changes. Small amounts of increase at first then it bolloons.

Regards Tim

 

[Norris68]

The reason for the database 'bloat' is that Access does not re-use space within the file. When you edit a form/report, new space is allocated within the file to hold the 'new' object and it's size increases. The old copy of the object is marked as deleted. When you compact your database, the deleted areas are not copied over, so your file size reduces. So in effect, the bloat is due to 'holes' in the database. Each time you save the form/report you create another 'hole' (and add to the file size)

(As cghoga suggests,) it is always a good idea to split your FE & BE - makes changes much easier to implement. I would also recommend issuing your FE as a MDE/ADE too.

 

[Blackshark]

Thanks Norris.

I will be splitting the smaller systems to FE & BE but the larger systems need to be able complete a report run (using PDF Pro to produce a few hundred management reports). If I FE/BE the larger three systems the performance is dire while producing reports.

Not the SQL fault!! (yeah I would say that) but our network is beyond description-aly bad. I am currently trying to get my workstation upgraded to 100Mbps. I work for a multi billion pound company in the UK!! and they give me a 10bps connection.

Just to confirm though, the reports are run from a reasonablly beefy PC with a 100mbit conenction. Takes just over 3 hours to run with the database as 1 file, split it takes 7hours+. I have even started to rewrite the code so it makes a copy of itself on the C drive, runs the reports from that and then deletes that copy. Should be able to split it after that.



Thanks Tim