中止查询 [英] Aborted queries

问题描述

大家好。


你们有没有经历过更新查询（Access 2003），它们在完成之前就停止了？这只是最新的例子，而不是唯一的例子。


更具体地说，我正在运行一个查询，它会更新整个表中的两个字段，例如1200万条记录。它会持续一段时间，然后问通常的这会更新一些东西，你确定吗？题。我说是。它可以工作另外半个小时或者其他什么，然后没有明显的原因，就像我按下Break键一样停止。当我查看表格时，已经更新了300万条左右的记录，其余的没有。


表格不需要压缩。 （虽然我会在/更新完成时，特别是因为它正在更新索引并清空一个字段）。我知道（从痛苦的经历来看）它远远不是数据库（Access 2000文件格式）的大小限制（2GB）。


是什么给出了什么？！也许某种缓冲问题？我知道这不是可用的RAM量（堆左），但我也知道Access有时显然有一些非常奇怪的内存限制。我在过去遇到错误，建议我增加注册表中的最大记录锁，但是这种情况看起来很不一样。


如果它有任何帮助，这里是'实际的查询我正在运行 - 那些涉及我的其他线程的人无疑会认出它...

展开 | 选择 | Wrap | 行号

解决方案

查询错误后查看数据库的大小。


猜测我会说你再次达到了最大值。不要忘记访问在查询中使用内存。尝试旧的C& R并在之后立即运行查询。你可能会侥幸成功。


这就是我喜欢唱片集的原因。似乎没有同样的问题。


玛丽

大家好。


你们有没有经历过更新查询（Access 2003），它们在完成之前就停止了？这只是最新的例子，而不是唯一的例子。


更具体地说，我正在运行一个查询，它会更新整个表中的两个字段，例如1200万条记录。它会持续一段时间，然后问通常的这会更新一些东西，你确定吗？题。我说是。它可以工作另外半个小时或者其他什么，然后没有明显的原因，就像我按下Break键一样停止。当我查看表格时，已经更新了300万条左右的记录，其余的没有。


表格不需要压缩。 （虽然我会在/更新完成时，特别是因为它正在更新索引并清空一个字段）。我知道（从痛苦的经历来看）它远远不是数据库（Access 2000文件格式）的大小限制（2GB）。


是什么给出了什么？！也许某种缓冲问题？我知道这不是可用的RAM量（堆左），但我也知道Access有时显然有一些非常奇怪的内存限制。我在过去遇到错误，建议我增加注册表中的最大记录锁，但是这种情况看起来很不一样。


如果它有任何帮助，这里是'实际的查询我正在运行 - 那些涉及我的其他线程的人无疑会认出它...

展开 | 选择 | Wrap | 行号

查询错误后查看数据库的大小。

猜测我会说你再次达到了最大值。不要忘记访问在查询中使用内存。尝试旧的C& R并在之后立即运行查询。你可能会侥幸成功。

这就是我更喜欢唱片集的原因。似乎没有同样的问题。

Mary

好​​的，按顺序排序......

• 不，它远远没有达到规模限制。我在1.7GB数据库和500MB数据库上遇到了同样的问题。
• 我可以在没有压缩的情况下重新运行更新，它（有时）运行正常。事实上，它今天下午完成了，无需担心其他9M +记录。大小从大约1.7GB增加到大约1.8GB。
• 不确定你对记录集的意思。请解释一下？

好​​的，按顺序排序...

• 否，它远远没有达到规模限制。我在1.7GB数据库和500MB数据库上遇到了同样的问题。
• 我可以在没有压缩的情况下重新运行更新，它（有时）运行正常。事实上，它今天下午完成了，无需担心其他9M +记录。大小从大约1.7GB增加到大约1.8GB。
• 不确定你对记录集的意思。请解释一下？

我的意思是，使用DAO：


1.打开记录集正在更新的查询

2.在更新所有记录时，按记录运行记录集记录。

3.使用以下方法将适当的字段设置为新值：


。编辑

rs！FieldName = NewValue

。更新


DoEvents


4. doevents命令应该在继续之前处理更新。这似乎提高了速度，它可能会清除缓冲区，因为它不确定。


5.循环到下一条记录，直到EOF


根据我的经验，任何时候我都必须运行动​​作查询来更新大量记录，这似乎可以提高速度。


Mary

Hi all.

Have any of you experienced update queries (Access 2003) which simply stop before finishing? This is just the latest example, not the only one.

To be more specific, I am running a query which updates two fields in an entire table of something like 12 million records. It chugs along for a while, then asks the usual "this will update something, are you sure?" question. I say yes. It works for another half-hour or whatever, then for no apparent reason, simply stops as though I had pressed the Break key. When I look at the table, 3 million or so records have been updated, the rest not.

The table does not need compacting. (Though I will if/when the update is complete, especially since it''s updating indexes and "nulling out" a field). I know (from bitter experience) that it is nowhere near the size limit (2GB) for a database (Access 2000 file format).

What gives?! Some sort of buffer problem, perhaps? I know it''s not the amount of RAM available (heaps left), but I also know that Access has some apparently very weird memory limitations at times. I have in the past hit errors suggesting I increase the maximum record locks in the registry, but this case appears quite different.

If it''s any help, here''s the actual query I''m running - those involved in my other threads will undoubtedly recognise it...

Expand|Select|Wrap|Line Numbers

解决方案

Check out the size of your database after the query bugs out.

At a guess I''d say you''re hitting the max size again. Don''t forget access uses memory in queries. Try the old C&R and run query immediately afterwards. You might get away with it.

This is why i prefer recordsets. Doesn''t seem to have the same problem.

Mary

Hi all.

Have any of you experienced update queries (Access 2003) which simply stop before finishing? This is just the latest example, not the only one.

To be more specific, I am running a query which updates two fields in an entire table of something like 12 million records. It chugs along for a while, then asks the usual "this will update something, are you sure?" question. I say yes. It works for another half-hour or whatever, then for no apparent reason, simply stops as though I had pressed the Break key. When I look at the table, 3 million or so records have been updated, the rest not.

The table does not need compacting. (Though I will if/when the update is complete, especially since it''s updating indexes and "nulling out" a field). I know (from bitter experience) that it is nowhere near the size limit (2GB) for a database (Access 2000 file format).

What gives?! Some sort of buffer problem, perhaps? I know it''s not the amount of RAM available (heaps left), but I also know that Access has some apparently very weird memory limitations at times. I have in the past hit errors suggesting I increase the maximum record locks in the registry, but this case appears quite different.

If it''s any help, here''s the actual query I''m running - those involved in my other threads will undoubtedly recognise it...

Expand|Select|Wrap|Line Numbers

Check out the size of your database after the query bugs out.
At a guess I''d say you''re hitting the max size again. Don''t forget access uses memory in queries. Try the old C&R and run query immediately afterwards. You might get away with it.
This is why i prefer recordsets. Doesn''t seem to have the same problem.
Mary

Ok, taking things in order...

• No, it''s nowhere near the size limit. I have had the same problem on a 1.7GB database and a 500MB database.
• I can re-run the update without compacting, and it will (sometimes) run ok. In fact it did this afternoon, getting through the other 9M+ records no worries. The size increased from around 1.7GB to around 1.8GB.
• Not sure what you mean about recordsets. Please explain?

Ok, taking things in order...

• No, it''s nowhere near the size limit. I have had the same problem on a 1.7GB database and a 500MB database.
• I can re-run the update without compacting, and it will (sometimes) run ok. In fact it did this afternoon, getting through the other 9M+ records no worries. The size increased from around 1.7GB to around 1.8GB.
• Not sure what you mean about recordsets. Please explain?

All I mean by this is, Using DAO:

1. Open a recordset of the query being updated
2. As all records are being updated, run through the recordset record by record.
3. Set appropriate field to new value using:

.Edit
rs!FieldName = NewValue
.Update

DoEvents

4. The doevents command should process the update before moving on. This seems to increase speed, it may be that it clears the buffer as it goes along I''m not sure.

5. Loop to next record until EOF

In my experience any time I''ve had to run an action query to update large amounts of records this seems to increase the speed.

Mary

这篇关于中止查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！