acCmdCompactDatabase

[Howie Jones]

To keep users out of the menu system I'm trying to compact my database automatically on exit.

This can be coded from within Access with 'DoCmd.RunCommand acCmdCompactDatabase'. But when the code runs I get an error message saying that this can't be done from within code and that I should use the menus. The same result comes when I try to use a macro.

Notwithstanding the question of why Access allows you to build code and macros that it clearly does not support, does anyone have a method for compacting the data automatically from with Access?

 

[Rich]

Assuming you have A2k or later you have the option to compact on close, is that not an option for you?

 

[Howie Jones]

Thanks for that reminder.

I'd actually used this option on one database but not the other.

I'm sure a nice lie-down in a darkened room will restore my sanity.

 

[cesarney]

Compact Database

Hi

This code in a form's button... (Access 2000)

some modifications because the language(portuguese)

but, works fine

in Acess 2002 don't..

bye

cesarney

========================================
General: Compact Current Database
Author(s)
Juan M. Afan de Ribera
========================================

For Access 2000 and Access 2002. Here is a snippet of code for compacting the current database. It uses the accDoDefaultAction method, that performs the
specified object's default action, and can be run from a command button on a form.

accDoDefaultAction is a method of the hidden IAccessible Class in Office library.


' ***** Code Start *****
Public Sub CompactDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Sub
' ***** Code End *****

 

[sdawson]

Multiple users

What is the effect on the db when u run compact on close where there a multiple users?
I would like to run this when the last users closes the db.
Possible?

 

[Howie Jones]

Interesting question.

I've not had to think about this yet as the server I was planning to use, so all users accessed the one database, is completey unstable. They're currently using individual databases on their own machines and I collate the data once a week into a master database: there are only 3 users.

Not the ideal solution, but it does mean we don't have all our eggs in one basket.

I'd be interested in any answers.

 

[craw]

MSAccess '97

I have often wondered this question, compact a database automatically... is this possible for v97? My help file doesn't recognize acCmdCompactDatabase.
And to add to sdawson, does it matter if the db is split? which end gets compacted?

 

[sdawson]

Compact on close

Can any senior members comment on the compact on close where there are multiple users?
Are there any links for this?

 

[craw]

acCmdCompactDatabase is available in '97 - however i am prompted by a window to choose the Db i want to compact ~~ i want this to happen automatically. I've tried using both of these. but the .runcommand doesn't support arguments. any suggestions out there?

Dim db As Database
Set db = CurrentDb
    
    DoCmd.RunCommand acCmdCompactDatabase, "db"

AND

Dim db As Database
Set db = CurrentDb
    
    DoCmd.RunCommand "db", acCmdCompactDatabase

 

[Howie Jones]

See the comment from Rich above, I think compact on exit is available in '97.

 

[craw]

no luck so far... i've looked under 'options' and 'startup'

 

[Howie Jones]

Just ran up an old copy of Access 97 and you're right, I couldn't find an option to 'compact on exit' either.

As Access doesn't seem to support macro or VB compacting from within a database it looks like the only way you can do this is from the menus.

For those interested I'll be putting a multi-user db on one of our servers next week so I should be able to get some answers on the use of 'compact on exit' with multiple users.

 

[craw]

Howie~~ thanks for the info

 

[Fornatian]

As I recall Rich posted a simple solution to this eons ago, and it was to use the dredded sendkeys command to do Tools-Database-CompactDatabase.

SendKeys "%tdc"