Simulate Compact on Close

[kabaka]

Hi,

I'm running A97 and an application that creates / deletes tables & queries
fairly frequently. I have a navigational form that is open at all times and
only closes when the database itself is closed. From my readings on this
newsgroup, I am aware of a built in function "compact on close" available in
newer versions of Access. Pigs will fly before we upgrade to Office 2000 or
2003 (I work in a very large company & the cost....blah blah blah). Does
anyone have a suggestion (i.e. a little bit of code) that would help me to
simulate a compact on close.

I've struggled with this concept as I can't ever seem to overcome the fact
that until the database is closed the ldb exists and I can't compact via code
if the ldb exists and I want to compact before the database is closed (so the
code will run)....... you get the picture.

 

[kabaka]

Ok, I've made a little progress but could still use some pointers. After
much more searching on the subject, I now have the following code in the
close event on my navigational form:

Private Sub Form_Close()
CommandBars("Menu Bar").Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact Database").accDoDefaultAction
End Sub

Is there anyway to automate the "Database to Compact From" input form that
comes up or avoid it all together (i.e. if you manually go Tools - Database
Utilities - Compact Database you don't get it)?

 

['69 Camaro]

Hi.

Is there anyway to automate the "Database to Compact From" input form that
comes up or avoid it all together (i.e. if you manually go Tools - Database
Utilities - Compact Database you don't get it)?

Sorry. This will not work with Jet 3.51. The database _must_ be closed
before the compaction routine will run. For several excellent alternatives,
please see the following Web page:

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

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.

 

[Larry Daugherty]

Hi,

There is something on www.mvps.org/access I believe it's a VB based
solution and it must be started manually.

I wrote and still supply to my Acc97 clients an Access application that will
backup to a Day-of-the-week folder in a root folder of your choosing then
compact and restore a target application to its original folder. It
requires that the target application start the backup app as it's closing.
Works like a charm. I created it when the truth behind the claims of a
major New England bank IT department that they did "nightly backups" was
revealed: They did backup every night and overwrote any previously saved
backup. So the only backup you ever had was last night's. Our users didn't
detect their Thursday problems until the following Monday and then they were
screwed. In my paradigm, a backup is attempted when each user exits, it
will fail as long as there is another user in the database. When the last
user exits, it flies. It creates and appends to a log file so you can track
every major step and analyze failures. Given that your company won't spring
for current application software (I don't blame them, I still like Acc97
too) they probably wouldn't spring for 3rd party solutions either. If you
wanted to run it using the System Agent then you could put in a bunch of
MDBs for it to process.

You can write yourself a similar Access application and run it using the
System Agent to process a list of MDBs or call it at the exit of each
application to process just the MDB(s) for that application.

HTH

 

[kabaka]

bummer. Thanks for saving me more time though

Hi.


Sorry. This will not work with Jet 3.51. The database _must_ be closed
before the compaction routine will run. For several excellent alternatives,
please see the following Web page:

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

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.

 

['69 Camaro]

You're welcome. Even though it's not the answer you were hoping for, you can
help others save time when they have the same question in the future. If you
sign
in to Microsoft's Online Community first, then find your question and mark
any posts that are "Answers" to your question, others will be able to search
the database of "Answered Questions" and quickly find yours, and we'll get
credit for giving correct "Answers," too.

Thanks!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.