Code For Compact On Close?

  • Thread starter Thread starter Neil
  • Start date Start date
N

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.
 
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!
 
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
 
answered in thread "Code for Break on Unhandled Errors", dated 10/31/07, in
this newsgroup.
 
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.
 
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...).
Click to expand...

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 said:
Most enterprise Access applications are built on the front-end, back-
end model.
Click to expand...

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.
Click to expand...

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/
 
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."
Click to expand...

any front end that does either of the things you describe is one
that I would classify as poorly-designed.

Temporary data belongs in a temporary database, not in your front
end. Any other approach is a substandard design.
 
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.
Click to expand...

It's important, however, to compact your front end if the
proportions of the data tables in your back end change. If, for
instance, your queries in your front end are compiled when you have
1000 records in one table and 1000 records in another, and your
database grows to 2000 in one and 500,000 in the other, it's likely
that the compilation for queries using those two tables will be
sub-optimal. A compact will flag all queries for recompiling using
current table statistics the next time those queries are executed.

But it's very seldom that back end data changes so drastically in a
way that makes the original compilation plan inefficient. But it's
also important to remember that it's possible for that to happen.
 
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.
 
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.
 
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.
 
Well, if by that all data would be in the back end, that poster included a
temporary MDB file stored on the client's machine as part of the "back end,"
then I don't have a problem with what he wrote. But if he meant (as I
originally thought) that in a client/server situation any temporary data
would only be in the server database, then I would still disagree with him.
But you're probably right re. what he meant.
 
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.
Click to expand...

How is that position relevant to the issue of compacting a front
end? Keep in mind that Tony's solution creates the temp database on
the fly each time so it doesn't require compacting. Thus, even if
you count the temp database as part of your front end, there is
still never any need to compact the front end.

Which was, I thought, the issue in question, not whether or not "all
data would reside in the back end," which, so far as I can tell, no
one was really arguing in the sense that you are construing it.
 
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.
Click to expand...

Tony recreates his on the fly when needed. I keep an empty template,
tmp.bak, and copy it over top of the tmp.mdb when I want to replace
it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compact Database 3
Compact DB on close 1
Compact DB on close 2
Easy way to compact an Access 2003 database 1
compact on close option 7
Compacting Access database 2
Compact an Access Applicaton by checking Compact on Close 2
Compact on Exit 1

Back
Top