Re: Interesting approach for compacting on close

Hmmmm...interesting. I do make changes to the forms on the fly when they load. I have a routine, "SetCurrencyandDates" that goes through a form, and sets the proper date format that the user selects in the Setup screen (3 letter month, vs. 2 number month designation), and set's currency fields to the proper regional setting. I found years ago, that if you just set a field for Currency, it would show the US dollar in England, for whatever reason. So on the form's load or open (on format for reports), setting the field to currency then properly uses the local currency symbol. This could be fixed in Access 2007, but the legacy code is still there

I am offering a "Disable compact on close" option, just in the event it causes trouble. In my testing, setting a threshold on the size of the program .accde, then only firing the compact on close if it exceeds, seems to work like a charm.

Now this is what I call constructive stuff. I should comment out the SetCurrencyandDates function that runs when a form is opened and see if that has an affect on bloat (since I've already off-loaded the big / frequently used temp tables to a separate database).

Many thanks,

"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in message news:Xns9B48AFDB38DF7f99a49ed1d0c49c5bbb2@xxxxxxxxxxxxxxxx
"Andy" <PCESoft@xxxxxxxxxxxxxxx> wrote in
news:u5wOk.1761$%11.2@xxxxxxxxxxxxxxxxxxxx:

I don't think your post is very helpful, even though you feel so
strongly about the compacting feature (I guess Microsoft never
should have spent even a dollar on developing a compact for the
FE).

Certainly not as implemented. If there was an option to make it a
prompt, then it would be useful (though that could also be
annoying).

As I mentioned, the temp
tables are already off-loaded into a workspace.accdb (separate
database from the FE).

Then the bloating is not being caused by your temp tables, but by
some other design error.

Your suggestions on "Designing an App Properly" are really great.
I'm honored that a super-programmer has replied to this humble
topic; one who has designed his apps so perfectly, that they never
need updating, new features or enhancements.

Well, what are you doing in your app that's causing bloat? Creating
QueryDefs on the fly? Using transactions? Making design changes to
the front end in code?

I think, David, this topic is intended for those interested in
compacting problems & solutions and maybe not necessarily for
yourself (maybe those with large FE/BE applications with 100's of
forms, queries, reports, and on the order of 100,000 lines of
code).

I don't have any apps that approach that size. But size of the app
has nothing to do with whether or not the front end will bloat or
not. Yes, a fresh front end MDE will bloat some when it's brand-new
because of things like the compilation for saved QueryDefs, but that
bloat should stop after a certain time, and should never be very
great (not more than 100% growth over a few days/weeks of daily
use). If your app is bloating beyond that, you probably have a
design error, since a properly-designed app distributed as an MDE is
not going to bloat at all beyond the initial bloat.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

.

Relevant Pages

  • Re: Interesting approach for compacting on close
    ... compacted, and makes extensive use of temp tables, which tends to ... and it didn't solve the FE bloat. ... Then you have other design errors in your front end besides storing ... for either the brain-dead stupid COMPACT ON CLOSE or to notify the ...
    (comp.databases.ms-access)
  • Re: Interesting approach for compacting on close
    ... compacted, and makes extensive use of temp tables, which tends to ... bloat the FE significantly. ... Then you have other design errors in your front end besides storing ... for either the brain-dead stupid COMPACT ON CLOSE or to notify the ...
    (comp.databases.ms-access)
  • Re: database bloat/size
    ... I've also imported all objects into a new file only to watch it bloat ... until I compact it again. ... Can the security part be done in VBA? ... Tony Toews, Microsoft Access MVP ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Replication Error after Adding New Table A2002
    ... Remember that design changes are serial, so that it tries to add the tables ... and then delete them (sometimes compact and repair of the DM can avoit that, ... Since the error, I've even deleted the new> tables, compact/repaired twice, and synch still fails with the "Design> changes could not be made..." ... BTW, this Design Master is a table-only> backend, and is not experiencing the Replication Conflict Viewer problem> that a different DM is producing. ...
    (microsoft.public.access.replication)
  • Re: Interesting issue with compaction. Any ideas to eliminate?
    ... > same directory on the server. ... that is the source of the bloat. ... Often corruption in the compiled code is hidden ... you never need to compact the front end. ...
    (comp.databases.ms-access)