Pretty simple question, I know.
A:
Check out this solution VBA Compact Current Database.
Basically it says this should work
Public Sub CompactDB()
CommandBars("Menu Bar").Controls("Tools").Controls ("Database utilities"). _
Controls("Compact and repair database...").accDoDefaultAction
End Sub
Dennis
2009-09-22 13:39:24
I'd strongly suggest using the direct VBA method.
Tony Toews
2009-09-22 18:28:01
+1
A:
Yes it is simple to do.
Sub CompactRepair()
Dim control As Office.CommandBarControl
Set control = CommandBars.FindControl( Id:=2071 )
control.accDoDefaultAction
End Sub
Basically it just finds the "Compact and repair" menuitem and clicks it, programatically.
Dale Halliwell
2009-09-22 13:40:07
A:
I did this many years back on 2003 or possibly 97, yikes!
If I recall you need to use one of the subcommands above tied to a timer. You cannot operate on the db with any connections or forms open.
So you do something about closing all forms, and kick off the timer as the last running method. (which will in turn call the compact operation once everything closes)
If you haven't figured this out I could dig through my archives and pull it up.
Eddie
2009-09-22 14:25:52
+2
A:
If you want to compact/repair an external mdb file (not the one you are working in just now):
Application.compactRepair sourecFile, destinationFile
If you want to compact the database you are working with:
Application.SetOption "Auto compact", True
In this last case, your app will be compacted when closing the file.
My opinion: writting a few lines of code in an extra MDB "compacter" file that you can call when you want to compact/repair an mdb file is very usefull: in most situations the file that needs to be compacted cannot be opened normally anymore, so you need to call the method from outside the file.
Otherwise, the autocompact shall by default be set to true in each main module of an Access app.
In case of a disaster, create a new mdb file and import all objects from the buggy file. You will usually find a faulty object (form, module, etc) that you will not be able to import.
Philippe Grondier
2009-09-22 14:42:16
COMPACT ON CLOSE is worthless in any properly designed Access app (it's only the back end that needs compacting, and you never have that open in the front end), and downright dangerous, since you don't get a chance to skip it (compacts can cause certain kinds of corrupt but still accessible data to be permanently lost).
David-W-Fenton
2009-09-23 01:41:52
When you use your client database to hold temporary data (such as local tables for example), compacting the file on close makes sense.
Philippe Grondier
2009-09-23 07:06:21
interesting stuff David. I have compact on close set, but might have to review that. Had a moment where it hung while compacting yesterday, killed the process and wondered if it had corrupted my db. Sighed in relief as I realised it compacts to a temp file, but still.
Nick
2009-09-23 08:30:24
When does one open a temporary database? Don't you have it linked to your application database, rather than opening it directly? Or are you storing all your UI objects in your temp database? I just don't see the point of COMPACT ON CLOSE. If I've been hammering a datastore with a bunch of UPDATE/APPEND/DELETE operations, I know to back it up and compact it. You *do* back up before a compact, right?
David-W-Fenton
2009-09-24 07:28:04
I was talking of temporary tables, not temporary databases. I have noticed that, when you regularly create temporary tables on your front end (being imported data from you back end, or runtime data such as a menu table, built at loggin time out of a global menu table with options granted depending on the user, or local 'available connections' table uploaded from an xml file somewhere in the network, or ...) the client side file has a tendance to grow, which is limited by setting the compactOnClause option. We do not back up before compacting, as we have no "permanent" data on the client side.
Philippe Grondier
2009-09-24 08:04:15
A:
When the user exits the FE attempt to rename the backend MDB preferably with todays date in the name in yyyy-mm-dd format. Ensure you close all bound forms, including hidden forms, and reports before doing this. If you get an error message, oops, its busy so don't bother. If it is successful then compact it back.
See my Backup, do you trust the users or sysadmins? tips page for more info.
Tony Toews
2009-09-22 18:37:03
A:
There's also Michael Kaplan's SOON ("Shut One, Open New") add-in. You'd have to chain it, but it's one way to do this.
I can't say I've had much reason to ever want to do this programatically, since I'm programming for end users, and they are never using anything but the front end in the Access user interface, and there's no reason to regularly compact a properly-designed front end.
David-W-Fenton
2009-09-23 01:44:53
nice strategic thought. Sometimes though, you just want it to be a self contained file. a portable tool.
Nick
2009-09-23 08:35:42