Database Bloat

[WC Justice]

I have an mdb file that is linked to SQL Server (about 50 tables), has 4
small queries and 4 small forms. No reports, but numerous modules.
Basically, this mdb will be running 24/7 and executing code on demand which
uses automation to open and populate a Word document, convert it to a pdf and
then rename the pdf and move it to another network folder.

It just recently compacted from 73mb to less than 10mb. I'm afraid that,
once it is online 24/7, that it will have to be compacted regularly. I would
prefer to tune my code so that it doesn't bloat.

As far as I can tell, I'm closing recordsets and setting them to nothing,
etc. Any other tips, or is there some silver bullet method that deflates
everything at the end of a subroutine?

 

[Stefan Hoffmann]

hi,

As far as I can tell, I'm closing recordsets and setting them to nothing,
etc. Any other tips, or is there some silver bullet method that deflates
everything at the end of a subroutine?

Set auto compact, disable the auto name correction. Close and restart
the application once a day.


mfG
--> stefan <--

 

[Jerry Whittle]

Access is like a paper bag and not a balloon. Whereas a balloon deflates when
the air is left out of it, Access stays the same basic size until you
manually 'refold' it by doing a compact and repair.

Access needs a little elbow room for sorting and grouping records. If this
is what is causing the bloat, the database will grow to the needed size
shortly after it's in use again. I suggest monitoring the database file size
for a while after going 24/7. As long as it stays under 1GB in size, there
shouldn't be any real problems.

Make sure that your queries are pass-though so that the actual work is done
on the SQL Server machine.

Also make sure that all your code is compiled as uncompiled code might cause
some of the problem.