What version of Access are you using? Up to Access 97, You would have to close the DB before compacting. Having said that, if you are the only user, you could use the SendKeys method. After Access 97, you can compact and repair on closing the DB. Go to Tools>options and I think it is on the view tab. The Microsoft Knowledge base have code for compacting and repairing at a scheduled time.
Originally posted by syk
Tools --> Options --> "General" tab
There is a tick box (bottom left hand) for "Compact on Close", check it, and OK
That would affect all databases, not always desireable. Moreover, mostly you would want to ensure that this happens for all users specirfying it in the db itself. With the above general method, that one needs to be set on each user's computer, and may have to be manyally reset after a reinstall, etc....