VBA code to compact and repair Access 2010 database on exit?

Hi Experts

I need to run a "Compact and repair" on my access database when the database closes.
Does anyone know how to do this using vba?

Who is Participating?
MacroShadowConnect With a Mentor Commented:
Application.SetOption "Auto compact", True

Open in new window

EirmanConnect With a Mentor Chief Operations ManagerCommented:
There is a option to "Compact On Close" in the "Current Database" section of options - no vba needed.
I'm not sure if this, or MacroShadow's solution actually repairs the database.

Don't forget to decompile your frontend periodically during development.
simsima_7876Author Commented:
Where do I put that?
In the OnExit section?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Eirman is correct, the code I posted will set the "Compact On Close" in the "Current Database" section of options.
The OP requested VBA so I provided the VBA solution.
The code has to be run once it doesn't matter when or where (from the immediate window is fine too).
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Just to clear some things up for the OP.

Access has a built-in "compact on close" option that when set, will compact the database every time it is closed if that option is "on".   MacroShadow's VBA sets that option on.

 From that point forward, the DB will compact on close every time.   That may be not what you want.

If your looking to optionally compact on close, the short answer it cannot be done in the way you think because you cannot compact the DB you currently  have open.

 The only way to compact a DB that your running in is to "shell out" to another DB; you start another DB, thus closing the current DB, that DB performs the compact, then either quits or re-opens the original DB.

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Also note that if you're running a split configuration (i.e. a Backend with only Tables, and a Frontend with Forms, Reports, etc), then setting Compact on Close in the FE will have no impact on the BackEnd, and that's generally what you want to compact. You'd have to do that manually, after all other users are logged out of the system.

Also be aware that not all bloat is necessarily a bad thing. After you compact your database and then run it for the first time, Access may "bloat" a good bit, but that doesn't really mean it's bad or corrupt. Access needs working space on the disk (for indexes and other internal processes), and will often expand to accommodate those objects. This initial bloat isn't a bad thing, and compacting will just force Access to perform that task again the next time the database is opened.

"Bad bloat" would be where Access has been in use for some time, and you're seeing significant increases in size over a very short time span (say a few days with multi-mb growth, perhaps). This would indicate troubles, and you should certainly perform maintenance when doing this.

Roger Carlson has an interesting article on this:

DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
FWIW ... here is the VBA code to Compact & Repair a given database from another database (that runs the VBA code).  This is the code (snippet) I use in our nightly backup of some 40+ databases on our share drive.

I use ShellWait because this code is run in a loop, Compacting & Repairing 40+ dbs, so this insures the C&R of a db completes before the next one starts.

Dim sCmd As String
Dim sLwsPath  As String
sLwsPath = "TheFolderPathAndDbNameYouWantToCompactAndRepair"

sCmd = Chr(34) & SysCmd(acSysCmdAccessDir) & "Msaccess.exe" & Chr(34) & " " & Chr(34) & sLwsPath & "\" & sFile & Chr(34) & " /COMPACT"

Call ShellWait(sCmd, vbMinimizedNoFocus)

Public Sub ShellWait(Pathname As String, Optional WindowStyle As Long)
    Dim start As STARTUPINFO
    Dim ret As Long
    ' Initialize the STARTUPINFO structure:
    With start
        .cb = Len(start)
        If Not IsMissing(WindowStyle) Then
            .dwFlags = STARTF_USESHOWWINDOW
            .wShowWindow = WindowStyle
        End If
    End With
    ' Start the shelled application:
    ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
            NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    ''If ret = WAIT_TIMEOUT Then MsgBox "TIMEOUT"
    ret& = CloseHandle(proc.hProcess)
End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.