compact and repair back end db

[johnqzsmith]

I have developed a desktop (single user) Access application. I am looking for a method to compact and repair a back end db in the easiest manner possible. I am using Access XP with a front end of approx 80 MB and a back end in the 7MB- 12 MB range.

I am compacting the front end by using:

Application.SetOption "Auto Compact", False

in the main program form OnUnload property . This seems to be working fine.

I have read conflicting forum messages about the ability to compact and repair the back end database from “inside” the front end. I would prefer to handle this in the same procedure (OnUnload) mentioned above if at all possible.

When the (linked) BE is open, there is an .ldb file open. I tried use the “Compact on Close” setting (ToolsOptionsGeneral tab) but this does not seem to work.

Any suggestions out there? I have sample mdb’s that can compact and repair from “outside” (i.e. when the front end and back end db’s are closed), but for my users, the best thing would be to have this automated as much as possible. This points to having it done upon close of the front end.

Thanks in advance,

JQ