My frontend continues to grow even after compacting 1

[mcarbone]

I've got an Access XP database frontend with 300 pages of VBA code, 100+ forms, 400 queries, and 300 reports in the frontend. It remained at under 30 meg for months. Over the past few months since I've added a number of new forms, reports, and code it's grown to 300 meg. I know I've taken Access to its limits but it seems that I have to open every single form, report, and query, save each one and it shrinks it about a hundred meg. Is there another alternative?

I've decompiled, compiled, compacted... Nothing works anymore.

Thanks,
Mark

 

[Steve101]

Shock horror! It seems that the application might be bio degrading.

Seriously though, you need to be seriously considering ways where you can reduce the numbers of objects / overall size. This may need to be done over a period, in a "low risk" manner. Some examples:

(a) Remove any objects which you can identify with certainty as unused. I usually put a ZDel prefix in front of them, then physically delete them after a week or two, when I know that they have no impact on the system.

(b) Ensure that your forms / reports do not have large embedded images in them. These can consume large amounts of space.

(c) Use linked tables, as opposed to embedded tables.

(d) See whether creating a new database and then importing all objects into it reduces the size.

(e) Consider whether it is possible to split the application into two or more applications, based on required functionality, different users etc. You dont talk about your tables and whether they're linked or not, but they should be; if they are, you could for example separate the application into a Transaction system (eg. to enter / maintain the data), and an Enquiry System.

(f) Consider whether its possible to reduce the number of queries / reports by using more powerful generic techniques; eg. replacing multiple hard coded queries with single parameter driven queries.

All in all, you need to do some serious strategising. I would'nt feel comfortable maintaining such a system (unless it was very well written, used good naming conventions, was well documented ... and needed to be that complex).

Hope this helps a little,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Stickarm]

Another thing to keep an eye on is any "Make Table" queries that you may be using, especially as the source for reports. Each time this process is run it expands the size of the database. Even though in many of these processes, "temp" tables are deleted, the database still grows each time it is run.

Phil

 

[Garridon]

Also if you are tracking Name Autocorrect and enabled Log Autocorrect Changes, that can cause some serious size issues. I deleted out the table Autocorrect created, and my database went down by over half.