vba 更新记录集时以及可能在其他点时“超出系统资源”
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/16089759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"System resource exceeded" when updating recordset, and possibly at other points
提问by sigil
I'm experiencing an intermittent issue with an application that's Excel 2010 front-end, Access 2010 back end. It's in use by 5-10 users simultaneously. Recently, users have started intermittently receiving the following error:
我在使用 Excel 2010 前端、Access 2010 后端的应用程序时遇到间歇性问题。它同时被 5-10 个用户使用。最近,用户开始间歇性地收到以下错误:
Run-time error '3035': System resource exceeded.
Run-time error '3035': System resource exceeded.
Sometimes the Debug button is grayed out so I can't jump to the code that caused the error, but when it's available to click, it takes me to the following code:
有时调试按钮变灰,所以我无法跳转到导致错误的代码,但是当它可以点击时,它会带我到以下代码:
'Open connection to back end DB
Set db = OpenDatabase(dbPath)
'Open a recordset of a table
Set RS = db.OpenRecordset(Tbl)
'loop through rows in a 2D array
For i = FR To LR
RS.AddNew
'loop through columns of the 2D array
For j = 1 to LC
'set values for various fields in the new record, using values from the array
Next
RS.Update
Next
Here, the RS.Update
is marked as the line that's causing the error.
在这里,RS.Update
被标记为导致错误的行。
What's odd is that this problem comes and goes; users will repeatedly receive it when attempting to submit a certain data set, then, several hours later, when they try to submit the same data set again, the operation succeeds without the error. It's also perplexing that sometimes the Debug button is available and sometimes it isn't.
奇怪的是,这个问题来来去去;用户在尝试提交某个数据集时会反复收到它,然后,几个小时后,当他们再次尝试提交相同的数据集时,操作成功且没有错误。同样令人困惑的是,有时“调试”按钮可用,有时则不可用。
One issue might be the size of the Access back end; it's currently ~650 MB, and we didn't start getting these messages until it grew to around 600 MB.
一个问题可能是 Access 后端的大小;它目前约为 650 MB,我们直到它增长到 600 MB 左右才开始收到这些消息。
Any ideas as to what could be causing this? Various Google hits indicate that this problem sometimes happens when a join query has too many fields, but this is just a recordset of a table, not a join query.
关于可能导致这种情况的任何想法?各种谷歌搜索表明,当连接查询有太多字段时,有时会发生此问题,但这只是表的记录集,而不是连接查询。
回答by Renaud Bompuis
Ahh, methink that this is one of the strange errors that pop-up when you write a lot to a backend database that can't keep up with the management of the lock file.
啊,我想这是当您向后端数据库写入大量内容无法跟上锁定文件的管理时弹出的奇怪错误之一。
The solution is to make sure you keep a connection open to the back-end database from each of your client and that you hold onto that connection until you close the client.
Just open a recordset to a table (say a dummy table with only one record), and keep that recordset open until you close the application.
解决方案是确保从每个客户端保持与后端数据库的连接打开,并保持该连接直到关闭客户端。
只需打开一个记录集到一个表(假设一个只有一条记录的虚拟表),并保持该记录集打开,直到您关闭应用程序。
Resource-wise, keeping this connection open will have no detrimental effect on performance or memory consumption, but it will ensure that the lock file is not continuously created/deleted every time a connection is open then closed.
在资源方面,保持此连接打开不会对性能或内存消耗产生不利影响,但它会确保每次连接打开然后关闭时不会连续创建/删除锁定文件。
Keeping that connection open will also substantially increase the performance of your data access.
保持该连接打开还会显着提高数据访问的性能。
Edit:
编辑:
You should be more explicit when using recordsets and specify exactly the mode of operation you need:
使用记录集时应该更明确,并准确指定所需的操作模式:
Set RS = db.OpenRecordset(Tbl, dbOpenTable, dbFailOnError)
or, faster if you are only appending data:
或者,如果您只是附加数据,则更快:
Set RS = db.OpenRecordset(Tbl, dbOpenTable, dbAppendOnly + dbFailOnError)
Also make absolutely sure you close the recordset once you're finished with appending the data!:
还要确保在完成附加数据后关闭记录集!:
Set RS = db.OpenRecordset(Tbl, dbOpenTable, dbAppendOnly + dbFailOnError)
With RS
'loop through rows in a 2D array
For i = FR To LR
.AddNew
'loop through columns of the 2D array
For j = 1 To LC
'set values for various fields in the new record,
'using values from the array
Next
.Update
Next
.Close
End With
Set RS = Nothing
回答by TBA
This is caused by running out of available virtual memory (VM) aka swap disk. A 32 bit app cannot use more than 2gb and for some reason Access uses a lot of VM and when it needs more and cannot get any then you run out of system resources.
这是由于可用的虚拟内存 (VM) 又名交换磁盘用完造成的。一个 32 位的应用程序不能使用超过 2GB 的空间,并且出于某种原因,Access 使用了大量的 VM,当它需要更多而无法获得时,系统资源就会耗尽。
Solution is to make sure your VM is at least 4 times the RAM and to restart your PC at least daily, only this clears out the VM from garbage left lying around from other apps.
解决方案是确保您的 VM 至少是 RAM 的 4 倍,并至少每天重新启动您的 PC,只有这样才能从其他应用程序留下的垃圾中清除 VM。
You will never have had this issue on a 32 bit OS, its only now with 64 bit OS that this happens.
在 32 位操作系统上您永远不会遇到此问题,只有现在在 64 位操作系统上才会发生这种情况。
回答by Jim Moore
Short StoryMy 3035 error was caused by an add.new to a file without a primary key.
短篇小说我的 3035 错误是由 add.new 到没有主键的文件引起的。
Composing a copy of the file with a primary key assigned and replacing the key-less file appears to have corrected the problem.
使用分配的主键编写文件副本并替换无键文件似乎已更正问题。
Long StoryI have been experiencing 3035 error on an Add.New to an existing backend table with >81,000 records. After searching the web for ideas and coming up dry I reflected on possible issues.
龙的故事我一直在经历着一个Add.New 3035错误现有的后端表> 81,000条记录。在网上搜索想法并得出结论后,我反思了可能存在的问题。
I compacted/repaired the backend files to no affect. Then decided to check the file design. It turns out there was no primary key assigned.
我压缩/修复了后端文件没有任何影响。然后决定检查文件设计。结果发现没有分配主键。
Assigning a primary key to the auto number ID field caused the same 3035 error! So I copied the data structure to a new file, assigned a primary key to the new file and then did a query append of the original file to the new file. Finally I renamed the files.
给自动编号 ID 字段分配主键导致同样的 3035 错误!所以我将数据结构复制到一个新文件,为新文件分配一个主键,然后将原始文件查询追加到新文件。最后我重命名了文件。
Using the new file appears to be working.
使用新文件似乎有效。