Opening Access DB with /compact operator

[koltregaskes]

I believe this is a questioned asked many times but I've yet to find a solution. I'd like to compact my Access DB before I run a few macros in it. I know there are various ways of doing this but everything I've tried doesn't work. I noticed that you can add /compact operator to a shortcut to the DB to open, compact and close the DB. So is there anyway you can run this? My current code is:

Dim CompactDB
CompactDB = Shell("C:\PM REPORT\PM Report Generator 2.accdb", vbNormalFocus)

If I try and add the /compact operator either inside the speech marks our after it gives me an error. Any ideas if there is a way to get this working?

I've had lots of problems running Access queries from Excel but swapped the Access queries to macros and that seems to work but I still get errors that are fixed once I compact the DB. I'd like the compacting to be automated.

 

[Andrew Poulsom]

I found this example by Googling for 'shell access compact':

Compact & Repair another database using shell

 

[koltregaskes]

Thank you very much, that looks like it works.