Compacting DB error

[JT]

I want to enable users to compact a database after importing a large file.
Below is the code 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.

 

[lwells]

Move the message box to the beginning of your code

MsgString = "The database will be compacted for better performance."
MsgBox MsgString, , "Database Compacted"

run the message box prior to compacting.

 

[lwells]

Also if you haven't done so, be sure to declare your variable MsgString

 

[Coyote]

How do you transform the code if you use a front-end and want to compact the
back-end ???

 

[John Gray]

"Coyote" wrote

How do you transform the code if you use a front-end
and want to compact the back-end ???

See Roger's Access Library:
http://rogersaccesslibrary.com/TableOfContents3.asp

Look for "CompactBackEndDB.mdb ( expert )"


John