How to compact an MS Access database, while the database is open, using vba

I am running a few modules of VBA code. In the middle of running the code crashes as Access reaches its max size of 2GB; but, if I compress the database at that point it is only 200MB.

Is it possible to compress the 开发者_高级运维database at regular intervals while the code is running?

I recently stumbled into this question, and some things I encounter in answers here are just plain wrong:

1. You CAN'T compact and repair an access database through VBA while it's open! No matter if all tables are closed, if you have an exclusive lock, etc.
2. You can, however, compact a backend from a linked database, if all connections to it are closed. This is why Tony Toews could successfully compact and repair.

This is unfortunate, and the easiest workaround by far is to create a linked database. But if this is undesirable, there is one alternate thing you can do, if you're willing to do some weird trickery.

The problem is that the main database has to be closed while the compact and repair happens. To work around this, we can do the following:

1. Programmatically create a VBScript file
2. Add code to that file so we can compact & repair our database without having it open
3. Open and run that file asynchronously
4. Close our database before the compact & repair happens
5. Compact and repair the database (creating a copy), deleting the old one, renaming the copy
6. Reopen our database, continue the batch
7. Delete the newly created file

Public Sub CompactRepairViaExternalScript()
    Dim vbscrPath As String
    vbscrPath = CurrentProject.Path & "\CRHelper.vbs"
    If Dir(CurrentProject.Path & "\CRHelper.vbs") <> "" Then
        Kill CurrentProject.Path & "\CRHelper.vbs"
    End If
    Dim vbStr As String
    vbStr = "dbName = """ & CurrentProject.FullName & """" & vbCrLf & _
    "resumeFunction = ""ResumeBatch""" & vbCrLf & _
    "Set app = CreateObject(""Access.Application"")" & vbCrLf & _
    "Set dbe = app.DBEngine" & vbCrLf & _
    "Set objFSO = CreateObject(""Scripting.FileSystemObject"")" & vbCrLf & _
    "On Error Resume Next" & vbCrLf & _
    "Do" & vbCrLf & _
    "If Err.Number <> 0 Then Err.Clear" & vbCrLf & _
    "WScript.Sleep 500" & vbCrLf & _
    "dbe.CompactDatabase dbName, dbName & ""_1""" & vbCrLf & _
    "errCount = errCount + 1" & vbCrLf & _
    "Loop While err.Number <> 0 And errCount < 100" & vbCrLf & _
    "If errCount < 100 Then" & vbCrLf & _
    "objFSO.DeleteFile dbName" & vbCrLf & _
    "objFSO.MoveFile dbName & ""_1"", dbName" & vbCrLf & _
    "app.OpenCurrentDatabase dbName" & vbCrLf & _
    "app.UserControl = True" & vbCrLf & _
    "app.Run resumeFunction" & vbCrLf & _
    "End If" & vbCrLf & _
    "objFSO.DeleteFile Wscript.ScriptFullName" & vbCrLf
    Dim fileHandle As Long
    fileHandle = FreeFile
    Open vbscrPath For Output As #fileHandle
    Print #fileHandle, vbStr
    Close #fileHandle
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    wsh.Run """" & vbscrPath & """"
    Set wsh = Nothing
    Application.Quit
End Sub

This does all the steps outlined above, and resumes the batch by calling the ResumeBatch function on the database that called this function (without any parameters).

Note that things like click-to-run protection and antivirus/policy not liking vbscript files can ruin this approach.

Yes, you can. However you have to close all recordset and database variables, forms and reports that are using the database file. You can test this yourself by running your code and seeing if the LDB file no longer exists. Also all users other than yourself have to be out of the database file of course.

You can loop through the Forms collection (which is actually the open forms) and the Reports collection to clsoe them all. Of course, once you're finished processing, you will need to reopen any autostart forms, etc, etc.

I'm not sure about vb, but I always did it from a command line by calling the "/compact" commandline switch. I think you can call the db via commandline switches just the same as you open any file in vb easily.

Per MS

Compacts and repairs the Access database, or compacts the Access project, specified before the /compact option and then closes Access. If you omit a target file name following the /compact option, the file is compacted to the original name and folder. To compact to a different name, specify a target file. If you don't include a path in target database or targe Access project, the target file is created in your My Documents folder by default. In an Access project, this option compacts the Access project (.adp) file but not the SQL Server database.

This worked in previous versions of MS-Access, but fails since we run Access 2010.

Sub CompactRepair()
Dim control As Office.CommandBarControl
Set control = CommandBars.FindControl(Id:=2071)
control.accDoDefaultAction
End Sub

Of course, the mdb will be closed, compressed and re-opened. Since Access 2010, the compress-operation must have been adapted by Microsoft.

Hope this helps.

You can only compact a database that isn't open (or, compact it to a differently-named file).

See here for steps.

According to Microsoft you can compact an open Access database so long as the file is opened exclusively.

See http://support.microsoft.com/kb/288631 Limitations of Compacting.