I would like to compact on close only if the database size goes over a
certain amount, rather than each time. Thus, I'd like to check the file size
and then perform the compact through code as the mdb's closing. Is that
possible?

I suppose one option would be to set the Compact On Close option in the
switchboard's On Close event, and then clear it whenever the database is
opened. That would probably work. But I'd prefer a cleaner solution, where I
can just call the compact function as the database is closing (I'm guessing
this isn't possible...).

Thanks.

answered in comp.databases.ms-access.


"Neil" <(E-Mail Removed)> wrote in message
news:PZTUi.1252$(E-Mail Removed)...
> I would like to compact on close only if the database size goes over a
> certain amount, rather than each time. Thus, I'd like to check the file
size
> and then perform the compact through code as the mdb's closing. Is that
> possible?
>
> I suppose one option would be to set the Compact On Close option in the
> switchboard's On Close event, and then clear it whenever the database is
> opened. That would probably work. But I'd prefer a cleaner solution, where
I
> can just call the compact function as the database is closing (I'm
guessing
> this isn't possible...).
>
> Thanks.
>
>

To set the Compact on Close option, you must deal with menu options, but you
can use the following to compact during the close event of the last form
open.

http://www.mvps.org/access/general/gen0041.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Neil" <(E-Mail Removed)> wrote in message
news:PZTUi.1252$(E-Mail Removed)...
>I would like to compact on close only if the database size goes over a
>certain amount, rather than each time. Thus, I'd like to check the file
>size and then perform the compact through code as the mdb's closing. Is
>that possible?
>
> I suppose one option would be to set the Compact On Close option in the
> switchboard's On Close event, and then clear it whenever the database is
> opened. That would probably work. But I'd prefer a cleaner solution, where
> I can just call the compact function as the database is closing (I'm
> guessing this isn't possible...).
>
> Thanks.
>

Thanks! That's good to know. I'd never seen the menu bars accessed that way,
so that's good to know too. (And a very creative use of the line break
character as well! :-) ) Thanks!

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%23F4Hu%(E-Mail Removed)...
> To set the Compact on Close option, you must deal with menu options, but
> you can use the following to compact during the close event of the last
> form open.
>
> http://www.mvps.org/access/general/gen0041.htm
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "Neil" <(E-Mail Removed)> wrote in message
> news:PZTUi.1252$(E-Mail Removed)...
>>I would like to compact on close only if the database size goes over a
>>certain amount, rather than each time. Thus, I'd like to check the file
>>size and then perform the compact through code as the mdb's closing. Is
>>that possible?
>>
>> I suppose one option would be to set the Compact On Close option in the
>> switchboard's On Close event, and then clear it whenever the database is
>> opened. That would probably work. But I'd prefer a cleaner solution,
>> where I can just call the compact function as the database is closing
>> (I'm guessing this isn't possible...).
>>
>> Thanks.
>>
>
>

Thanks again, Arvin. Now I need one for setting the Break On Unhandled
Errors option in VBA Tools | Options? Do you know of anything? Thanks!

Neil


"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%23F4Hu%(E-Mail Removed)...
> To set the Compact on Close option, you must deal with menu options, but
> you can use the following to compact during the close event of the last
> form open.
>
> http://www.mvps.org/access/general/gen0041.htm
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "Neil" <(E-Mail Removed)> wrote in message
> news:PZTUi.1252$(E-Mail Removed)...
>>I would like to compact on close only if the database size goes over a
>>certain amount, rather than each time. Thus, I'd like to check the file
>>size and then perform the compact through code as the mdb's closing. Is
>>that possible?
>>
>> I suppose one option would be to set the Compact On Close option in the
>> switchboard's On Close event, and then clear it whenever the database is
>> opened. That would probably work. But I'd prefer a cleaner solution,
>> where I can just call the compact function as the database is closing
>> (I'm guessing this isn't possible...).
>>
>> Thanks.
>>
>
>

answered in thread "Code for Break on Unhandled Errors", dated 10/31/07, in
this newsgroup.


"Neil" <(E-Mail Removed)> wrote in message
news7aWi.13094$(E-Mail Removed)...
> Thanks again, Arvin. Now I need one for setting the Break On Unhandled
> Errors option in VBA Tools | Options? Do you know of anything? Thanks!
>
> Neil
>
>
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:%23F4Hu%(E-Mail Removed)...
> > To set the Compact on Close option, you must deal with menu options, but
> > you can use the following to compact during the close event of the last
> > form open.
> >
> > http://www.mvps.org/access/general/gen0041.htm
> > --
> > Arvin Meyer, MCP, MVP
> > http://www.datastrat.com
> > http://www.mvps.org/access
> > http://www.accessmvp.com
> >
> > "Neil" <(E-Mail Removed)> wrote in message
> > news:PZTUi.1252$(E-Mail Removed)...
> >>I would like to compact on close only if the database size goes over a
> >>certain amount, rather than each time. Thus, I'd like to check the file
> >>size and then perform the compact through code as the mdb's closing. Is
> >>that possible?
> >>
> >> I suppose one option would be to set the Compact On Close option in the
> >> switchboard's On Close event, and then clear it whenever the database
is
> >> opened. That would probably work. But I'd prefer a cleaner solution,
> >> where I can just call the compact function as the database is closing
> >> (I'm guessing this isn't possible...).
> >>
> >> Thanks.
> >>
> >
> >
>
>

If only I'd waited another hour!... Thanks for your reply there (and here!).

"tina" <(E-Mail Removed)> wrote in message
news:dwbWi.310118$(E-Mail Removed)...
> answered in thread "Code for Break on Unhandled Errors", dated 10/31/07,
> in
> this newsgroup.
>
>
> "Neil" <(E-Mail Removed)> wrote in message
> news7aWi.13094$(E-Mail Removed)...
>> Thanks again, Arvin. Now I need one for setting the Break On Unhandled
>> Errors option in VBA Tools | Options? Do you know of anything? Thanks!
>>
>> Neil
>>
>>
>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>> news:%23F4Hu%(E-Mail Removed)...
>> > To set the Compact on Close option, you must deal with menu options,
>> > but
>> > you can use the following to compact during the close event of the last
>> > form open.
>> >
>> > http://www.mvps.org/access/general/gen0041.htm
>> > --
>> > Arvin Meyer, MCP, MVP
>> > http://www.datastrat.com
>> > http://www.mvps.org/access
>> > http://www.accessmvp.com
>> >
>> > "Neil" <(E-Mail Removed)> wrote in message
>> > news:PZTUi.1252$(E-Mail Removed)...
>> >>I would like to compact on close only if the database size goes over a
>> >>certain amount, rather than each time. Thus, I'd like to check the file
>> >>size and then perform the compact through code as the mdb's closing. Is
>> >>that possible?
>> >>
>> >> I suppose one option would be to set the Compact On Close option in
>> >> the
>> >> switchboard's On Close event, and then clear it whenever the database
> is
>> >> opened. That would probably work. But I'd prefer a cleaner solution,
>> >> where I can just call the compact function as the database is closing
>> >> (I'm guessing this isn't possible...).
>> >>
>> >> Thanks.
>> >>
>> >
>> >
>>
>>
>
>

you're welcome. and btw, my previous post was not a scolding of any kind; i
posted it for the benefit of others who might read this thread.


"Neil" <(E-Mail Removed)> wrote in message
news:UedWi.3325$%(E-Mail Removed)...
> If only I'd waited another hour!... Thanks for your reply there (and
here!).
>
> "tina" <(E-Mail Removed)> wrote in message
> news:dwbWi.310118$(E-Mail Removed)...
> > answered in thread "Code for Break on Unhandled Errors", dated 10/31/07,
> > in
> > this newsgroup.
> >
> >
> > "Neil" <(E-Mail Removed)> wrote in message
> > news7aWi.13094$(E-Mail Removed)...
> >> Thanks again, Arvin. Now I need one for setting the Break On Unhandled
> >> Errors option in VBA Tools | Options? Do you know of anything? Thanks!
> >>
> >> Neil
> >>
> >>
> >> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> >> news:%23F4Hu%(E-Mail Removed)...
> >> > To set the Compact on Close option, you must deal with menu options,
> >> > but
> >> > you can use the following to compact during the close event of the
last
> >> > form open.
> >> >
> >> > http://www.mvps.org/access/general/gen0041.htm
> >> > --
> >> > Arvin Meyer, MCP, MVP
> >> > http://www.datastrat.com
> >> > http://www.mvps.org/access
> >> > http://www.accessmvp.com
> >> >
> >> > "Neil" <(E-Mail Removed)> wrote in message
> >> > news:PZTUi.1252$(E-Mail Removed)...
> >> >>I would like to compact on close only if the database size goes over
a
> >> >>certain amount, rather than each time. Thus, I'd like to check the
file
> >> >>size and then perform the compact through code as the mdb's closing.
Is
> >> >>that possible?
> >> >>
> >> >> I suppose one option would be to set the Compact On Close option in
> >> >> the
> >> >> switchboard's On Close event, and then clear it whenever the
database
> > is
> >> >> opened. That would probably work. But I'd prefer a cleaner solution,
> >> >> where I can just call the compact function as the database is
closing
> >> >> (I'm guessing this isn't possible...).
> >> >>
> >> >> Thanks.
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

On Oct 28, 12:11 am, "Neil" <nos...@nospam.net> wrote:
> I would like to compact on close only if the database size goes over a
> certain amount, rather than each time. Thus, I'd like to check the file size
> and then perform the compact through code as the mdb's closing. Is that
> possible?
>
> I suppose one option would be to set the Compact On Close option in the
> switchboard's On Close event, and then clear it whenever the database is
> opened. That would probably work. But I'd prefer a cleaner solution, where I
> can just call the compact function as the database is closing (I'm guessing
> this isn't possible...).

Perhaps, for beginners it's worthwhile ot point out why (IMO) this
capability (check size and if big, compact) isn't built in to Access.
Most enterprise Access applications are built on the front-end, back-
end model. I expect others might disagree, but in my experience a well
designed front-end will not grow in size, because no data is held
there, and no operations that require temporary data objects will run
there. So the front-end will need compacting no more often than the
user has some spare time and says, "Gee, maybe I should compact this
thing." The back end can be compacted regularly; we only have to
ascertain that no one has opened it exclusivley before we do so; this
can be built into code rather easily.

I completely disagree with you. One of the advantages of using an Access MDB
file over, say, an ADP, is the ability to use temporary tables for report
generation and other ad-hoc uses.

Also, some implementations of client-server apps using an MDB front end will
copy static lookup tables from the server machine when the database is
opened, and then use those local tables for lookup, instead of making calls
across the network for lookup tables.

So I disagree that "a well designed front-end will not grow in size."


"lyle" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Oct 28, 12:11 am, "Neil" <nos...@nospam.net> wrote:
>> I would like to compact on close only if the database size goes over a
>> certain amount, rather than each time. Thus, I'd like to check the file
>> size
>> and then perform the compact through code as the mdb's closing. Is that
>> possible?
>>
>> I suppose one option would be to set the Compact On Close option in the
>> switchboard's On Close event, and then clear it whenever the database is
>> opened. That would probably work. But I'd prefer a cleaner solution,
>> where I
>> can just call the compact function as the database is closing (I'm
>> guessing
>> this isn't possible...).
>
> Perhaps, for beginners it's worthwhile ot point out why (IMO) this
> capability (check size and if big, compact) isn't built in to Access.
> Most enterprise Access applications are built on the front-end, back-
> end model. I expect others might disagree, but in my experience a well
> designed front-end will not grow in size, because no data is held
> there, and no operations that require temporary data objects will run
> there. So the front-end will need compacting no more often than the
> user has some spare time and says, "Gee, maybe I should compact this
> thing." The back end can be compacted regularly; we only have to
> ascertain that no one has opened it exclusivley before we do so; this
> can be built into code rather easily.
>
>