Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-12-08
9
Medium Priority
?
694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 27

Accepted Solution

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

Open in new window

0
 
LVL 24

Assisted Solution

by:Eirman
Eirman earned 248 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 248 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 248 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 248 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

609 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