Compacting via VBA (Shell stays open)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am currently using this line in the module to close the current database,
which has the option "Compact on Close" enabled.
{Application.CloseCurrentDatabase}
The DB compacts as expected but leaves the empty Access window open, which
keeps the process from launching the next day. I've tried adding the
following in an effort to close the shell {application.quit} but it does not
have any effect. (probably because the database is compacting). What am I
doing wrong? Thanks in Advance!

BTW...to those MVP's that follow these newsgroups.. You ROCK! I can not
imagine not having this source of support or having to pay thru the nose for
it.
 
When you say you "add the following", do you mean you add that line beneath
the CloseCurrentDatabase call? If so, try replacing the CloseCurrentDatabase
with Application.Quit and see what happens.
 
When I use just Application.Quit the databse does not compact. This db needs
to compact every day due to the number of records that pass thru it on a
daily basis, or I'd just forego the compaction.
 
Drayton said:
I am currently using this line in the module to close the current
database, which has the option "Compact on Close" enabled.
{Application.CloseCurrentDatabase}
The DB compacts as expected but leaves the empty Access window open,
which keeps the process from launching the next day. I've tried
adding the following in an effort to close the shell
{application.quit} but it does not have any effect. (probably because
the database is compacting). What am I doing wrong? Thanks in Advance!
Click to expand...

Assuming you're using Access 2000 or later -- which you must be, or you
wouldn't have the Compact On Close option -- you can run a line of code
to compact the current database and reopen it. See this link:

http://www.mvps.org/access/general/gen0041.htm

You could use that in place of the Compact On Close option, if this is
the only reason you have that option set. I'm not sure from your post
whether you really do want to close the database and exit Access until
it is restarted the next day, or whether this solution (which would
compact and reopen the database) will suit you.
 
Dirk,


Dirk Goldgar said:
Assuming you're using Access 2000 or later -- which you must be, or you
wouldn't have the Compact On Close option -- you can run a line of code
to compact the current database and reopen it. See this link:

http://www.mvps.org/access/general/gen0041.htm

You could use that in place of the Compact On Close option, if this is
the only reason you have that option set. I'm not sure from your post
whether you really do want to close the database and exit Access until
it is restarted the next day, or whether this solution (which would
compact and reopen the database) will suit you.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Dirk,
I am trying to compact the database at the end of the process and
close the entire application. I have tried the compact database method you
described, but when the db comes back up, the autoexec process launches,
re-initiating my data import and record parsing. I assume that since the
database actually closes during the compact process, that it will not come
back to the module that called the compact. I'm leaning towards setting a
flag at the very end of the process that will kill the import and parse
portions of the autoexec macro from running if it is set to true and then
reset the flag. Am I trying this the hard way or should I just close the
database and set up a new process that will compact this db (and a few
others) sometime after the daily processes are complete? I sure would like to
have the db compact and the application shut down upon completion of its
process without having to introduce yet another scheduled process into the
mix.
 
Drayton said:
Dirk,
I am trying to compact the database at the end of the process
and close the entire application. I have tried the compact database
method you described, but when the db comes back up, the autoexec
process launches, re-initiating my data import and record parsing. I
assume that since the database actually closes during the compact
process, that it will not come back to the module that called the
compact. I'm leaning towards setting a flag at the very end of the
process that will kill the import and parse portions of the autoexec
macro from running if it is set to true and then reset the flag. Am I
trying this the hard way or should I just close the database and set
up a new process that will compact this db (and a few others)
sometime after the daily processes are complete? I sure would like to
have the db compact and the application shut down upon completion of
its process without having to introduce yet another scheduled process
into the mix.
Click to expand...

I see. Borrowing the same idea used in that link I posted, try this:

'----- start of code -----
Sub CloseDatabaseAndAccess()

CommandBars("Menu Bar"). _
Controls("File"). _
Controls("Exit"). _
accDoDefaultAction

End Sub

'----- end of code -----

It works for me: it closes the current database, compacting it if
Conpact On Close is set, and exits Access.
 
That Worked.. and much simpler than I had thought it would be. I take it that
the same method would work for any command available from the menu bar. I
really need to quit hacking and actually start digging into the VBA a little
more, but just as soon as I get one process working, I am tasked with
another, with little time left over for polishing the app much less teaching
myself VBA. Thanks again for your timely and accurate responses!
 
Drayton said:
That Worked.. and much simpler than I had thought it would be. I take
it that the same method would work for any command available from the
menu bar.
Click to expand...

I believe it ought to, but I can't swear to it. This and compaction are
the only ones I've tried. There could be exceptions.
I really need to quit hacking and actually start digging
into the VBA a little more, but just as soon as I get one process
working, I am tasked with another, with little time left over for
polishing the app much less teaching myself VBA.
Click to expand...

I know the feeling. Good luck!
Thanks again for
your timely and accurate responses!
Click to expand...

You're welcome.
 
I just got to this, but if compact on Close is enabled.

have a button that does

docmd.quit

that will do what you need.
 
Steven Britton via AccessMonster.com said:
I just got to this, but if compact on Close is enabled.

have a button that does

docmd.quit

that will do what you need.
Click to expand...

You're right! DoCmd.Quit behaves differently than Application.Quit.
That's simpler, I'd say, than all that mucking about with the command
bar. Good answer.
 
Back
Top