database 越来越多的 MS Access 文件大小问题

Growing MS Access File Size problem

提问by user55886

I have a large MS Access application with a lot of computations in VBA code. When I run it it eventually crashes due to excessive file size. There are a lot of intermediate tables and queries created and subsequently deleted, but Access does not reclaim the space. I have diligently closed all intermediate record sets and set all temporary objects to nothing, but nothing helps. The only way I can get my code to run is to run part of it, stop and repair/compress the file then restart the code.

我有一个大型 MS Access 应用程序，其中包含大量 VBA 代码计算。当我运行它时，它最终会因文件过大而崩溃。有很多中间表和查询创建并随后被删除，但 Access 不回收空间。我已经努力关闭所有中间记录集并将所有临时对象设置为空，但没有任何帮助。让我的代码运行的唯一方法是运行它的一部分，停止并修复/压缩文件，然后重新启动代码。

Isn't there a better way?

没有更好的办法吗？

Thanks

谢谢

回答by Mitchel Sellers

You should be able to run the compact function from within your VBA code.

您应该能够从 VBA 代码中运行压缩函数。

I had the below snippet bookmarked from a long time ago when I was doing access work.

很久以前，当我进行访问工作时，我将以下代码段添加为书签。

Public Sub CompactDB() 
    CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and repair database...").accDoDefaultAction 
End Sub 

You can put that in your code to get around it.

你可以把它放在你的代码中来解决它。

NOTE: you might also consider growing to a larger db system if you are having these types of scaling issues.

注意：如果您遇到这些类型的扩展问题，您也可以考虑扩展到更大的数据库系统。

回答by dkretz

What sizes are you dealing with? What is the error code when it crashes? I'd be surprised if it is simply because the file gets "too big", but I imagine there's a limit. It sounds from your description of all the temp stuff that there may be design improvements that would help.

你在处理什么尺寸？崩溃时的错误代码是什么？如果仅仅是因为文件“太大”，我会感到惊讶，但我想这是有限制的。从您对所有临时材料的描述中可以看出，设计改进可能会有所帮助。

EDIT: I expect you realize it's non-trivial to replace the database with something else - even if you try to keep whatever else is in the mdb besides the tables. Access querydefs are unique, Access SQL is non-standard and you'd be basically starting over.

编辑：我希望您意识到用其他东西替换数据库是非常重要的 - 即使您尝试保留 mdb 中除表之外的任何其他内容。Access 查询定义是唯一的，Access SQL 是非标准的，您基本上会重新开始。

Most Access applications I've seen have lots of opportunity for refactoring; and it's usually not that difficult if a) you understand the logic and the business rules, and b) you have a solid understanding of Access programming. But that would be more or less true for any alternatives. If I were you and you're a little short in either area, maybe you can get some help. But I'd try to rescue the Access app first.

我见过的大多数 Access 应用程序都有很多重构的机会；如果 a) 您了解逻辑和业务规则，并且 b) 您对 Access 编程有深入的了解，那么通常不会那么困难。但对于任何替代方案来说，这或多或少都是正确的。如果我是你，而你在这两个方面都有些欠缺，也许你可以得到一些帮助。但我会先尝试拯救 Access 应用程序。

There's also a suggestion from another poster about moving the tables into one or more attached MDBs. That's a solid, well-proven technique in general. But first I'd get a handle on what the real cause of the problem is.

另一位发帖人还建议将表格移动到一个或多个附加的 MDB 中。总的来说，这是一种可靠的、经过充分验证的技术。但首先我会了解问题的真正原因是什么。

回答by BIBD

I'd push the data over to MS SQL (the permanent data and the intermediate tables); and you can leave the code portion in MS Access for the time being.

我会将数据推送到 MS SQL（永久数据和中间表）；您可以暂时将代码部分留在 MS Access 中。

This solves two big issues:

这解决了两个大问题：

1. The data will be inherently more stable/dependable (I can't tell you how many times I've had a corrupt MS Access database).
2. Your Access database won't grow/change very much (it should reach an equilibrium once all the code in has been run and compiled).

1. 数据本质上将更加稳定/可靠（我无法告诉您我有多少次损坏的 MS Access 数据库）。
2. 您的 Access 数据库不会增长/变化太多（一旦运行并编译了所有代码，它应该达到平衡）。

Both of these mean no more having to compress/repair the database; you can get a free version (the Express Edition) of MS SQL and it is not that hard to do.

这两者都意味着不再需要压缩/修复数据库；您可以获得 MS SQL 的免费版本（速成版），这并不难。

回答by Philippe Grondier

If you do not want to switch to SQL Express or similar, you could dig the following ideas:

如果你不想切换到 SQL Express 或类似的，你可以挖掘以下想法：

• Open another 'external' access database (mdb file) for all temporary tables, so you could put all temp data in the external file, throwing away the mdb file when you close your app. You will then manipulate in your code the 'currentDb' object and another database that you build at startup and connect to through jet, OLEDB or ODBC connection
• Separate your permanent tables from your code and, when needed, bring the data into your local client interface to build your temporary tables. This can be done for example by linking the external database to the local/client file using "DoCmd.transferDatabase acLink". This can also be done by connecting to the permanent data through OLEDB connection, opening the needed recordset(s) and saving them locally as XML files. There are many other solutions that can be implemented here.

• 为所有临时表打开另一个“外部”访问数据库（mdb 文件），这样您就可以将所有临时数据放在外部文件中，并在关闭应用程序时丢弃 mdb 文件。然后，您将在您的代码中操作“currentDb”对象和您在启动时构建并通过 jet、OLEDB 或 ODBC 连接连接的另一个数据库
• 将永久表与代码分开，并在需要时将数据带入本地客户端界面以构建临时表。例如，这可以通过使用“DoCmd.transferDatabase acLink”将外部数据库链接到本地​​/客户端文件来完成。这也可以通过通过 OLEDB 连接连接到永久数据、打开所需的记录集并将它们在本地保存为 XML 文件来完成。还有许多其他解决方案可以在这里实施。

回答by David Henninger

The state of affairs with regard to Jet file sizes is interminably problematic for me.

Jet 文件大小的状况对我来说是无休止的问题。

I am currently watching a piece of my own VBA code from Access database A as it does a series of single-record field updatesusing ADO to a table on Access database B (via a updateable-query reference in database A). The single field is a CHAR(8). With every 4 updates that go by, database B grows by about 8 Kbytes. No good excuse for that. The addition to the file size slows performance on this severely; with each file growth, updates slow from about one per second (in a table of about 30-40K records using single-record SQL lookups and no indexes anywhere) to one per 5-10 seconds. Now, I admit, I did compact/repair database B prior to running this update code; perhaps if I had not done that, the performance would not have been this bad. Had the target field for update been of, say, type Memo, then I would have expected this. But to carry out an update on a CHAR() field and get this result is simply not reasonable.

我目前正在观看来自 Access 数据库 A 的一段我自己的 VBA 代码，因为它执行了一系列单记录字段更新对 Access 数据库 B 上的表使用 ADO（通过数据库 A 中的可更新查询引用）。单个字段是 CHAR(8)。每经过 4 次更新，数据库 B 就会增长大约 8 KB。没有什么好的借口。增加文件大小会严重降低性能；随着每个文件的增长，更新速度从大约每秒一个（在一个包含大约 30-40K 条记录的表中使用单记录 SQL 查找并且在任何地方都没有索引）慢到每 5-10 秒一个。现在，我承认，我在运行此更新代码之前压缩/修复了数据库 B；也许如果我没有这样做，表现就不会如此糟糕。如果更新的目标字段是 Memo 类型，那么我会预料到这一点。但是对 CHAR() 字段进行更新并得到这个结果是不合理的。

Most of the above (no particular criticism for any one solution intended) appear to be valid solutions for applications that use a relatively permanent business application arrangement (talk to the same target databases all of the time). Mine is not so . . . I cannot alter the target database (database B), as it is generated and consumed by a vendor's tool that we use to export and import data from their application.

以上大多数（对任何一种解决方案都没有特别的批评）对于使用相对永久的业务应用程序安排（始终与相同的目标数据库对话）的应用程序来说似乎是有效的解决方案。我的不是这样。. . 我无法更改目标数据库（数据库 B），因为它是由我们用来从他们的应用程序导出和导入数据的供应商工具生成和使用的。

I understand and commend the above writers for coming up with solutions to users' problems. However, I cannot let it stand when poorsoftware design/implementation gets in the way of users using a product as the users expect it to function.

我理解并赞扬上述作者提出解决用户问题的方法。然而，当糟糕的软件设计/实施妨碍用户使用产品时，我不能让它继续存在，因为用户希望它能够正常工作。

回答by RBILLC

I have encountered a similar issue where my database was bloating on raw data import. Instead of splitting the database and compacting the backend routinely, I decided to use the database object (DAO) to create a temp database, import the data, query/modify data in that temp database, pull it over to your original database via SQL and then delete it. YBase code shown below:

我遇到了一个类似的问题，我的数据库在导入原始数据时膨胀。我决定使用数据库对象 (DAO) 来创建临时数据库、导入数据、查询/修改临时数据库中的数据，然后通过 SQL 将其拉到原始数据库中，而不是常规地拆分数据库和压缩后端。然后删除它。YBase代码如下所示：

Sub tempAccessDatabaseImport()
    Dim mySQL As String
    Dim tempDBPath As String
    Dim myWrk As DAO.Workspace
    Dim tempDB As DAO.Database
    Dim myObject

    'Define temp access database path
    tempPathArr = Split(Application.CurrentProject.Path, "\")
    For i = LBound(tempPathArr) To UBound(tempPathArr)
        tempDBPath = tempDBPath + tempPathArr(i) + "\"
    Next i
    tempDBPath = tempDBPath + "tempDB.accdb"

    'Delete temp access database if exists
    Set myObject = CreateObject("Scripting.FileSystemObject")
    If myObject.FileExists(tempDBPath) Then
        myObject.deleteFile (tempDBPath)
    End If

    'Open default workspace
    Set myWrk = DBEngine.Workspaces(0)

    'DAO Create database
    Set tempDB = myWrk.CreateDatabase(tempDBPath, dbLangGeneral)

    'DAO - Import temp xlsx into temp Access table
    mySQL = "SELECT * INTO tempTable FROM (SELECT vXLSX.*FROM [Excel 12.0;HDR=YES;DATABASE=" & RAWDATAPATH & "].[" & WORKSHEETNAME & "$] As vXLSX)"

    'DAO Execute SQL
    Debug.Print mySQL
    Debug.Print
    tempDB.Execute mySQL, dbSeeChanges

    'Do Something Else

    'Close DAO Database object
    tempDB.Close
    Set tempDB = Nothing

    myWrk.Close
    Set myWrk = Nothing

    'Delete temp access database if exists
    If myObject.FileExists(tempDBPath) Then
        'myObject.deleteFile (tempDBPath)
    End If
End Sub

回答by duffymo

I'm not an MVP, but Google found these. Maybe they'll help you:

我不是 MVP，但 Google 发现了这些。也许他们会帮助你：

http://www.mvps.org/access/general/gen0041.htmhttp://forums.devarticles.com/microsoft-access-development-49/compact-database-via-vba-24958.html

http://www.mvps.org/access/general/gen0041.htm http://forums.devarticles.com/microsoft-access-development-49/compact-database-via-vba-24958.html

回答by Bramha Ghosh

Unfortunately, MS Access has problems when you get too large - I think the max size is 2GB for an access DB.

不幸的是，当您变得太大时，MS Access 会出现问题 - 我认为访问数据库的最大大小为 2GB。

You may consider moving to Sql Express, VistaDB, etc.

您可以考虑迁移到 Sql Express、VistaDB 等。

回答by Knox

According to http://office.microsoft.com/en-us/access/HP051868081033.aspx, Access 2003 and 2007 have a 2 GB limit. However, it's easy to move some or all the tables into a separate .mdb file and then link to those tables. It's good practice anyway to have two files, one for your data and one for all the macros, queries, and so on. You could even have multiple files if your table file gets near the 2 GB limit.

根据http://office.microsoft.com/en-us/access/HP051868081033.aspx，Access 2003 和 2007 有 2 GB 的限制。但是，很容易将部分或所有表移动到单独的 .mdb 文件中，然后链接到这些表。无论如何，最好有两个文件，一个用于您的数据，另一个用于所有宏、查询等。如果您的表文件接近 2 GB 的限制，您甚至可以拥有多个文件。