Code For Compact On Close?

[Neil]

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.

 

[tina]

answered in comp.databases.ms-access.

 

[Arvin Meyer [MVP]]

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

 

[Neil]

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!

 

[Neil]

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

 

[tina]

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

 

[Neil]

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

 

[tina]

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.

 

[lyle]

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.

 

[Neil]

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."

 

[Douglas J. Steele]

If you're using temporary tables in an MDB, you should put them in a
temporary database so that the front-end doesn't increase significantly in
size.

See the example Tony Toews has at
http://www.granite.ab.ca/access/temptables.htm

 

[Tony Toews [MVP]]

Most enterprise Access applications are built on the front-end, back-
end model.

I would say all but there are a few idiots out there.

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.

It's been my experience that the FE will grow by about 10% or 20% in the first few
days and thereafter stay the same size roughly. I've had clients who haven't
replaced or compacted the FE for a year or two.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Neil]

And the temporary database would still need to be compacted occasionally.

In other words, his argument was that all data would be in the back end, and
there would be no data in the front end (whether in the main or a temporary
database) to grow the database. That's what I was saying was wrong.

 

[Neil]

I would consider a temporary database (MDB file) a part of the front end,
even if not the main file. The SQL database would be the back end. So, if
you mean that temporary data should go in a separate MDB file on the client
machine, then you're agreeing with me that his argument that all data would
reside in the back end was wrong.

Whether in the main file or in a separate MDB file on the client machine,
the point I was making was that there was a need for temporary front end
(client) data in a well-designed system, and not all data would reside in
the back end.

 

[Douglas J. Steele]

The temporary database would technically be a second back-end, not a
front-end.

And since it's a temporary table, you'd delete the temporary database when
done with the table, not worry about whether or not it needs compacting.

 

[Neil]

No, not really. The original point that I was making was that data needs to
be stored on the client's machine. As I said in the message you replied to,
if the person I was replying to meant that temporary data could be stored in
a temporary file on the client machine, and that temporary file on the
client machine was part of the "back end" that he was referring to, then,
yes, I was wrong, because I misunderstood his point. But if he meant that
all data in a well-designed system would be stored in the server db, then
that was the original point I was countering.

In other words, there are two distinct issues here: first the compacting
issue, and second the "all data in the back end" issue, which is what I was
countering (since I thought that meant all data in the server db). If the
person had said, "You don't need a compact because temporary data could be
placed in a temp db on the client machine," then I wouldn't have disagreed
with him. But I thought he was saying that all temp data should go in the
server db, and that was why I countered his statement.

 

[Neil]

Do you perform a Kill first, or do you just copy it over on top of it?

 

[Neil]

Well, first, I hadn't reviewed Tony's solution when I made the statement.
So, yes, you're right about that. So, with Tony's solution (and not the
fixed-temp-db solution that I was thinking of), there would be no need for
compacting.

So, to put this issue to rest, let me say once and for all: I agree with
you. OK? There is no need for compacting if you delete the temporary db and
recreate it on the fly. The only point I was making (which I thought the
person wasn't saying) was that you need temp data on the client machine,
even in a well-designed system, and you can't always use the server db for
temp data. That was the only point I was making, really.

 

[Neil]

OK, apparently so. Still, that's what I was discussing.

 

[Neil]

OK, one more time (with feeling...). I thought the person was saying that in
a well-designed system, all data, including temp data, would go in the
server db. I did not realize that by "back end" he was including a temp db
on the client machine. I thought by "back end" he meant strictly the server
db. So I was making the point that temp data would go on the client machine,
and not in the server db. That was the only point I was making.

The further discussions in this thread made the distinction between putting
the temp data in the application file on the client machine vs. in a temp
file on the client machine, and I don't disagree with that the temp file
approach is a superior method. But I thought he was saying that temp data
would go into the server db, and that was the point I was disagreeing with.