What is the code  and what does it do when you try to run it?

A common way of compacting an Access database  is to automate the menu
choice.



CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities"). _

Controls("Compact and repair database...").accDoDefaultAction



The problem with doing this in Excel is that CommandBars refers to the
Excel object model rather than the Access object model.



A reference to the Access model is required and the command needs to be
changed to indicate that it refers to the Access model.

If you have a reference to Access from Excel, say

AccessApp.CommandBars("Menu Bar").Controls("Tools").Controls("Database
Utilities")

I haven't tried it, just a thought.

To compact an Access database  via VBA you need to make a copy of the
original, and compact from one to the other. When complete you can
delete the original. Here is code  from within Access, you will need to
reference MS-Access within Excel to accomplish this. Though I will admit
I never tried this via Excel.

Dim dbOriginal As String
Dim dbCompact As String
Dim dbPath As String
Dim dbTempPath As String

dbOriginal = "Inventory and Cost.mdb" 'Original Database
dbCompact = "Inventory and Cost1.mdb" 'Database to copy too
dbPath = "M:\Databases\" & dbOriginal 'Path of original database
dbTempPath = "M:\Databases\" & dbCompact 'Path of new database

Call DBEngine.CompactDatabase(dbPath, dbTempPath) 'Compact data
DoEvents
Kill dbPath 'Delete original
DoEvents
Name dbTempPath As dbPath 'Rename new database to original name

I copied and pasted the code  into that last email, and it didn't
come through looking right. I'll try this again, however if it still
looks funny in the email, I'm sure you can copy and paste into your
module and break it apart correctly.

Dim dbOriginal As String
Dim dbCompact As String
Dim dbPath As String
Dim dbTempPath As String

dbOriginal = "Inventory and Cost.mdb" 'Original Database
dbCompact = "Inventory and Cost1.mdb" 'Database to copy too
dbPath = "M:\Databases\" & dbOriginal 'Path of original database
dbTempPath = "M:\Databases\" & dbCompact 'Path of new database

Call DBEngine.CompactDatabase(dbPath, dbTempPath) 'Compact data
DoEvents
Kill dbPath 'Delete original
DoEvents
Name dbTempPath As dbPath 'Rename new database  to original name

--also read this to the end:
http://www.mdbmakers.com/forums/showthread.php?
t=574&highlight=compactdatabase

I have searched the same problem for a while.
Found this:
www.mdbmakers.com/forums/showthread.php?t=9382
but have not succeed.
Maybe you can get something out of it, I'll be happy to hear.

Well may be this VB code be a help

Application.CompactRepair SourceFile, DestinationFile [, LogFile]
'SourceFile = File to be compacted/repaired
'DestinationFile = the new compacted/repaired file
'LogFile = Optional (boolean) whether a log file should be created

Source:- recoveryandmanagement.wordpress.com/.../

Repair your corrupt database then try Recovery for Access tool to repair lost .mdb & .accdb files. It recover various database objects like tables, vies, macros, modules, queries, etc.It supports MS Access 2010, 2007, 2003, 2002 & 2000.

Try:- www.recoverydeletedfiles.com/...recovery-tool.html

All you need to do just read and follow the instruction posted here - www.filesrepairtool.com/access-file-repair.html