Excel -Access DB -ADO.内存泄漏->超出系统资源 [英] Excel -Access DB -ADO. Memory Leak-> System Resource Exceeded

问题描述

我使用 Excel VBA 来清理大型 csv 文件.为此，我将 csv 文件加载到 Access 数据库中，然后使用 sql 查询执行所有数据转换活动.所以抽象的过程是这样的

Im using Excel VBA to clean up large csv files. For that i load the csv files into access database then using sql queries i perform all data transformation activities. So the process in abstract goes like this

打开 Excel --> 在开始时单击创建访问数据库 --> 将 csv 文件加载到不同的表 --> 使用 adoddb 连接对数据库执行不同的 DDl 和 DML 语句 --> 输出最终数据.

我在这里面临的问题是内存使用量总是随着 excel 的增加而增加.似乎访问数据库处理也被添加到excel本身.所以最终我得到了错误 "System Resource Exceeded".

The problem I am facing here is that the memory usage always goes up by excel. It seems the access db processing is also added to excel itself. So ultimately i get the error "System Resource Exceeded".

每次执行查询时.内存使用率上升并且永远不会下降.查询涉及 3-4 个表中的大约 10k 到 100k 条记录.

Each time a query is executed. the memory usage goes high up and never comes down. The queries are on around 10k to 100k records in 3-4 tables.

为什么内存使用永远不会减少?

每次我执行 ddl/dml 查询时，我都会打开 adodb 连接并关闭它.我在使用后关闭所有记录集并设置为空.但内存使用量仍然没有下降.

Every time i do a ddl/dml query i open adodb connection and close it. I close all recordsets after use and set to nothing. But still the memory usage never comes down.

看到了不同的相关文章.但大多数人都在讨论同一个 excel 文件中的数据.就我而言，没有数据保存在内存或 Excel 文件中.

Saw different articles related. But most are discussing about data in the same excel file. In my case no data is kept in memory or in excel file.

我在这里看到了 Microsoft 的一篇文章，其中也谈到了 excel 中的数据本身.https://support.microsoft.com/en-us/kb/319998

I saw one article from Microsoft here which also talks about the data in excel itself. https://support.microsoft.com/en-us/kb/319998

有人知道有什么解决方法吗?

Does anybody know any workaround for this please?

例如:将数据从 csv 文件加载到表中，我使用以下代码

For eg: to load data to a table from csv file i use the below code

 StrSql = "SELECT * into " & TableName & " FROM [Text;FMT=Delimited;HDR=YES;DATABASE=" & DSPath & "].[" & DSName & "]"
    ExecuteSQL StrSql


Private Function ExecuteSQL(Sql As String) As Long
  Dim Con As ADODB.Connection
  Dim I As Long

  Connect Con
  Con.Execute Sql, I
  ExecuteSQL = I
  CloseCon Con
End Function

Public Sub CloseCon(ByRef Con As ADODB.Connection)
  If Not Con Is Nothing Then
     If Con.State = adStateOpen Then
        Con.Close
        Set Con = Nothing
     End If
  End If
End Sub

Public Sub Connect(ByRef Con As ADODB.Connection)
Dim ConStr As String

If Not Con Is Nothing Then
    If Con.State = adStateOpen Then
        Exit Sub
    End If
End If
On Error GoTo err
    CloseCon Con
    Set Con = New ADODB.Connection
    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile & ";Persist Security Info=False"
    Con.Open ConStr, , , -1
    Exit Sub
err:
End Sub

这有点帮助.

我没有在每次打开和关闭时使用单独的连接对象，而是尝试使用一个公共连接对象，该对象从一开始就打开，只有在进程完成时才关闭.这样一来，内存消耗减少了，进程运行的时间也更长了

推荐答案

您的代码看起来不错.问题是 Microsoft 在您的参考中的确认:无法通过关闭和释放 ADO 对象来回收 ADO 查询使用的内存.释放内存的唯一方法是退出 Excel.

Your code looks sound. The issue is the confirmation by Microsoft in your reference: "The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel.

所以我们必须时不时退出 Excel 以回收资源.

So we must quit Excel now and then to reclaim resources.

1. 退出 Excel"意味着必须关闭您正在使用的当前工作簿，或者

1. "Quit Excel" means that the current workbook from which you are working must be closed, or

退出 Excel"表示退出 Excel 的所有实例，以便有效地从内存中删除 Excel.

"Quit Excel" means to quit all instances of Excel so Excel is effecively removed from memory.

广告.1:在这种情况下，您可以创建一个父工作簿"来启动另一个包含 ADODB 处理部分的工作簿.它在部分处理后退出，您的父母开始一个新的、其他的工作簿，继续处理等.使用一些智能剪裁 &粘贴以调整何时退出每个工作簿.

Ad. 1: in this case you can create a "parent workbook" that starts another workbook containing a part of the ADODB processing. It quits after the partial processing and your parent starts a new, other, workbook that continues the processing, etcetera. Use some smart cut & paste to tune when to quit each workbook.

广告 2:在这种情况下，您使用例如Word 启动 Excel.Application 的新实例并以与 1 相同的方式继续.只是希望 MS-Office 没有集成到任何 Office 程序运行时 ADO DLL 不会退出...

Ad 2: in this case you use e.g. Word to start a new instance of Excel.Application and proceed in the same manner as under 1. Just hope that MS-Office is not so integrated that the ADO DLL will not quit when any Office program is running...

当然，向您的 IT 部门投诉 Microsoft 已确认的错误以安装 Access UI 可能会更好.

Of course, complaining with the Microsoft confirmed bug in hand to your IT department to have the Access UI installed might even be better.

这篇关于Excel -Access DB -ADO.内存泄漏->超出系统资源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！