compacting a database

[JT]

I want to compact my database programatically after importing a large file.
I have the following code for when the user clicks on a Compact Database
button. I
am now getting a message that says:

"You can't compact the open database, while running a macro or Visual Basic
code.
Instead of using the macro or code, on the Tools menu, point ot Database
Utilities, and then click Compact/Repair Database."
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Compact_Database_Click()

CompactDB

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub CompactDB()

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

MsgString = "The database has been compacted for better performance."
MsgBox MsgString, , "Database Compacted"

End Sub

Any suggestions or comments to alleviate this problem would be greatly
appreciated. Thanks for all of the help with this project.

 

[John Gray]

[...]

am now getting a message that says:

"You can't compact the open database,

[...]


That looks like the code from Juan M. Afan de Ribera (minus the messaging). It works
fine for me in an A2000 database.

My understanding of how this works is that Access (the application)
- closes the database (mdb)
- then does the compact routine
- then reopens the database

Assuming this is correct, I suspect there is no place for the message code here -- it
will interfere with the process. Try commenting it out, see what happens.

hth,
John

 

[John Gray]

[...]

am now getting a message that says:

"You can't compact the open database,

[...]


That looks like the code from Juan M. Afan de Ribera (minus the messaging). It works
fine for me in an A2000 database.

My understanding of how this works is that Access (the application)
- closes the database (mdb)
- then does the compact routine
- then reopens the database

Assuming this is correct, I suspect there is no place for the message code here -- it
will interfere with the process. Try commenting it out, see what happens.

hth,
John