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

Question

I am running several VBA code modules. In the middle of code execution, a crash occurs when the maximum access size is 2 GB; but if I am compressing the database at this point, it is only 200 MB.

Is it possible to compress the database at regular intervals while the code is executing?

Answer 1

Yes, you can. However, you need to close all records and databases, forms and reports that use the database file. You can verify this yourself by running your code and finding out that the LDB file no longer exists. In addition, all users except you must be outside the database file, of course.

You can scroll through the collection of forms (which is actually open forms) and the collection of reports to collect all of them. Of course, as soon as you finish processing, you will need to open any form of autorun again, etc. Etc.

Answer 2

This worked in previous versions of MS-Access, but it has worked since we launched Access 2010.

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

Of course, mdb will be closed, compressed and reopened. Starting with Access 2010, the compression operation must be adapted by Microsoft.

Hope this helps.

Answer 3

You can only compile a database that is not open (or compress it into a file with a different name).

See here for steps.

Answer 4

I'm not sure about vb, but I always did this from the command line, invoking the / compact command line switch. I think you can invoke db via command line switches in the same way as easily opening any file in vb.

In MS

Compact and restores the Access database, or compacts the access project specified before the / compact option, and then closes Access. If you omit the name of the target file by following the / compact option, the file will be compressed into the original name and folder. To compress to a different name, specify the target file. If you do not include the path in the target database or the Targe Access project, the target file is created in the My Documents folder by default. In an Access project, this option compresses the Access project file (.adp), but not the SQL Server database.

Answer 5

According to Microsoft, you can compile an open Access database while the file is open exclusively.

See http://support.microsoft.com/kb/288631 Compression restrictions.

Answer 6

I recently came across this question, and some of the things that I come across in the answers here are simply incorrect:

• CANNOT compactly and restore access database through VBA when it is opened! Regardless of whether all tables are closed, if you have an exclusive lock, etc.
• You can, however, compile the backend from the linked database if all connections to it are closed. That is why Tony Tows was able to successfully compress and recover.

This is unfortunate, and the simplest solution is to create a linked database. But if this is undesirable, there is one alternative thing that you can do if you are willing to do some kind of strange deception.

The problem is that the main database must be closed while the compression and repair is in progress. To get around this, we can do the following:

• Programmatically create a VBScript file
• Add code to this file so that we can compress and restore our database without opening it
• Open and run this file asynchronously
• Close our database before compression and recovery occurs.
• Compactness and restoration of the database (creating a copy), deleting the old one, renaming the copy
• Let's open our database, continue the package
• Delete 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 dbe = WScript.CreateObject(""DAO.DBEngine.120"")" & 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 & _ "Set app = CreateObject(""Access.Applition"")" & 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 

These are all the steps described above, and resumes the package by calling the ResumeBatch function in the database that called this function (without any parameters).

Please note that actions such as click protection and antivirus / policies that vbscript files do not like can ruin this approach.