Solved

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

Posted on 2014-12-08
9
142 Views
Last Modified: 2016-05-28
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?

Thanks
0
Comment
Question by:simsima_7876
9 Comments
 
LVL 26

Accepted Solution

by:
MacroShadow earned 252 total points
ID: 40486507
Application.SetOption "Auto compact", True

Open in new window

0
 
LVL 23

Assisted Solution

by:Eirman
Eirman earned 62 total points
ID: 40486528
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.
0
 

Author Comment

by:simsima_7876
ID: 40486530
Where do I put that?
In the OnExit section?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 26

Expert Comment

by:MacroShadow
ID: 40486533
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).
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 62 total points
ID: 40486542
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.

Jim.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 62 total points
ID: 40486607
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:

http://rogersaccessblog.blogspot.com/2010/12/should-i-use-compact-on-close-feature.html
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 62 total points
ID: 40487287
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 proc As PROCESS_INFORMATION
    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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now