Solved

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

Posted on 2014-12-08
9
210 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Date Formatting on Userform Print 5 26
Clear a Text Box 7 23
record saved form with no buttons or X 3 16
vba to flip column 14 17
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

808 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