Dean,
I'm not saying this cannot be done, maybe it can, I'm not sure... but it's
not necessarily a very good idea. Repairing a database from within itself
while it's open is kind of tricky, since Access has to close it to repair
it. Also, if it's a multi-user database, all users will be kicked out. I
take the view that it's better to prevent than to cure, so I would suggest
alternatively a regular compact and repair.
If this is a monolithic database (i.e. not split in a front and back end),
then just selecting the Compact On Close option (Tools, Options, tab
General) will do the job just fine, by compacting the database every time
you exit it.
If it is the back end of a split db the previous method will not work, so
you need to deal with this otherwise. One solution I use and which works
fine for me, is a small .exe (written in VB 6.0) which compacts the back end
db, and which I have put on a server and used the Scheduler to run it
overnight when there are no users accessing the db. If you don't have VB
Studio (or some other language that can produce an .exe) you can do the same
by means of another Access .mdb, used just to run the required code (fired
from an autoexec macro or On Open event of a dummy form that loads on db
open).
HTH,
Nikos
