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

Question

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?

Inez

Are you appending and deleting a lot of stuff during your processing? If so, you have a design error -- data that is appended and then deleted is temporary data and should never be appended to your real data file, but instead into another temp database. – Lohengrin

Answer 1

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.

Goodlooking

Answer 2

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.

Toccaratoccata

yes but all i am getting is that u cannot compact the database when its open or through vba – Inez

Then either your FE has something open against the database, which can include a combo box or listbox on an unbound form, or someone else has the database opened. I use this technique to do backups of the BE database from within th FE frequently. – Toccaratoccata

Answer 3

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.

Artina

The question is about compacting while the file is open. Your suggestion does not change the fact that that is simply impossible. – Lohengrin

Answer 4

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.

Frightful

Answer 5

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

See here for steps.

Footlights

Answer 6

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.

Sporadic