如何在数据库打开时使用 vba 压缩 MS Access 数据库

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

提问by tksy

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.

我正在运行一些 VBA 代码模块。在运行过程中，当 Access 达到 2GB 的最大大小时，代码崩溃；但是，如果我当时压缩数据库，它只有 200MB。

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

是否可以在代码运行时定期压缩数据库？

回答by Tony Toews

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.

是的你可以。但是，您必须关闭所有使用数据库文件的记录集和数据库变量、表单和报表。您可以通过运行代码并查看 LDB 文件是否不再存在来自己测试。当然，除了您自己以外的所有用户都必须在数据库文件之外。

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.

您可以遍历 Forms 集合（实际上是打开的表单）和 Reports 集合以将它们全部关闭。当然，完成处理后，您将需要重新打开任何自动启动表单等。

回答by Erik A

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

我最近偶然发现了这个问题，我在这里的答案中遇到的一些事情完全是错误的：

1. You CAN'Tcompact 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.

1. 您无法压缩和修复通过VBA的Access数据库，而它的开放！无论所有表是否关闭，是否有排他锁等。
2. 但是，如果到它的所有连接都已关闭，您可以从链接的数据库压缩后端。这就是 Tony Toews 能够成功压实和修复的原因。

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

1. 以编程方式创建 VBScript 文件
2. 将代码添加到该文件中，以便我们无需打开即可压缩和修复我们的数据库
3. 异步打开并运行该文件
4. 在压缩和修复发生之前关闭我们的数据库
5. 压缩和修复数据库（创建副本），删除旧的，重命名副本
6. 重新打开我们的数据库，继续批处理
7. 删除新创建的文件

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 ResumeBatchfunction on the database that called this function (without any parameters).

这将执行上述所有步骤，并通过调用ResumeBatch数据库上调用此函数的函数（不带任何参数）来恢复批处理。

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

请注意，诸如点击运行保护和不喜欢 vbscript 文件的防病毒/策略之类的东西可能会破坏这种方法。

回答by user9122800

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

这在以前版本的 MS-Access 中有效，但自从我们运行 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.

当然，mdb 将被关闭、压缩并重新打开。从 Access 2010 开始，压缩操作必须由 Microsoft 改编。

Hope this helps.

希望这可以帮助。

回答by Keng

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.

我不确定 vb，但我总是通过调用“/compact”命令行开关从命令行执行此操作。我认为您可以通过命令行开关调用数据库，就像在 vb 中轻松打开任何文件一样。

Per MS

每 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.

压缩和修复 Access 数据库，或压缩在 /compact 选项之前指定的 Access 项目，然后关闭 Access。如果在 /compact 选项后省略目标文件名，则文件将压缩为原始名称和文件夹。要压缩为不同的名称，请指定目标文件。如果您不在目标数据库或目标 Access 项目中包含路径，则默认情况下将在我的文档文件夹中创建目标文件。在 Access 项目中，此选项压缩 Access 项目 (.adp) 文件，但不压缩 SQL Server 数据库。

回答by Tony

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

根据 Microsoft 的说法，只要文件以独占方式打开，您就可以压缩打开的 Access 数据库。

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

请参阅http://support.microsoft.com/kb/288631压缩限制。

回答by Michael Todd

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

您只能压缩未打开的数据库（或将其压缩为不同名称的文件）。

See herefor steps.

请参阅此处了解步骤。