Solved

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

Posted on 2014-12-08
9
166 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 27

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 27

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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 …

920 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

11 Experts available now in Live!

Get 1:1 Help Now