Form和Recordset关闭后无法删除Temp Table [英] Unable to delete Temp Table after the Form and Recordset are closed
问题描述
我有一系列表单，允许用户更新临时表中的数据，然后删除永久表中的记录，并将临时表中的记录追加到永久表中。最后一步是删除临时表。我有一切正在删除临时表。我收到运行时错误''3211''：数据库引擎无法锁定表''Temporary_1''，因为它已被其他人或进程使用。
临时表是用户更新并运行此命令的表单的源。因此，在尝试删除临时表之前我们做的最后一件事是关闭表单。我想也许问题是我们在记录集中使用临时表，所以我们需要在删除表之前关闭记录集，但是当我尝试这样做时，我得到一个运行时错误''3704''操作关闭对象时不允许。这让我感到困惑，因为我打开记录集并且不关闭它，为什么不需要关闭它？
我没有在我的代码或表单中看到临时表正被使用并且应该被锁定的其他地方。非常感谢任何建议。
I have a series of forms that allow the users to update data in a temporary table and then delete the records in the permanent table and append the records from the temporary table to the permanent table. The final step is to delete the temporary table. I have everything working up to the deletion of the temporary table. I am receiving a run-time error ''3211'': The database engine could not lock table ''Temporary_1'' because it is already in use by another person or process.
The temporary table is the source for the form that the user updates and this command runs from. So, the last thing we do before attempting to delete the temporary table is close the form. I thought maybe the issue is that we use the temporary table in our recordset so, we would need to close the record set before deleting the table, but when I try to do this I get a Run-time error ''3704'' Operation is not allowed when the object is closed. This confuses me because I open the recordset and do not close it, why would it not need to be closed?
I do not see anywhere else in my code or my forms that the temporary table is being used and should be locked. Any advice is greatly appreciated.
推荐答案
这似乎是一个范围问题：
关闭你的记录集 关闭表格之前。
变量/指针只能在调用过程中的范围。
关闭表单时，会终止该范围内的指针。通常Access对此非常宽容;然而，当它变得暴躁时，小心......它会吃掉你的数据......（wakawakawakawakawakawakawakawaka --- burp）。
This appears to be a scope issue:
Close your record sets before you close the form.
The variables/pointers are only available within scope while within the calling procedure.
When you close the form, you kill the pointers within that scope. Normally Access is fairly forgiving about this; however, when it gets cranky, watch out.... it eats your data.... (wakawakawakawakawakawakawakawaka--- burp).
Zmbd，我把记录集的关闭移到了在表格关闭之前发生。我仍然遇到同样的错误：
Rst1不会关闭我得到运行时错误''3704''对象关闭时不允许操作。
表不会删除我得到运行时错误''3211''：数据库引擎无法锁定表''Temporary_1''，因为它已被其他人或进程使用。
我添加了Debug.Print rst1.State（第45,47行和第52行），结果总是为0，即使在我们打开rst1的第46行之后。我知道SQL语句（第39-42行）正在执行，因为记录被附加到表中。我不明白的是记录集的状态为0。这是因为SQL是一个插入？
如果记录集没有打开，并且我们没有在我们数据库的任何其他位置引用此表，则不应该关闭基于表的表单，将表关闭？表单是否为子表单并且我们正在关闭关闭子表单的主表单是否重要？
还有什么我应该寻找的吗？
Zmbd, I moved the closing of the recordsets to occur before the closing of the form. I am still getting the same errors:
Rst1 will not close I get a Run-time error ''3704'' Operation is not allowed when the object is closed.
Table will not delete I get a Run-time error ''3211'': The database engine could not lock table ''Temporary_1'' because it is already in use by another person or process.
I added Debug.Print rst1.State(lines 45,47,and 52) and the result is always 0 even after line 46 where we open rst1. I know that the SQL statement (line 39-42) is executing because the records are being appended into the table. What I don’t understand is the state of 0 for the recordset. Is that because the SQL is an Insert?
If the recordset is not open, and we are not referencing this table anywhere else in our database shouldn’t closing the form that is based on the table free up the table to be closed? Would it matter that the form is a subform and we are closing the main form which closes the sub form?
Is there something else I should look for?
对不起，
我没有发现sSQL是一个插入
因为它是一个动作查询，你将永远不会获得开放状态。我是猜测因为你使用了adLockOptimistic这个表被锁定了;但是，因为这是一个动作查询，指针在释放锁之前就丢失了。
让'试试：
原始代码块：
第44行删除
第46行更改：
Sorry,
I didn''t catch that sSQL was an insert
because it''s an action query you will never get an open state. I am guessing that because you used adLockOptimistic the table was locked; however, because this is an action query, the pointer was lost before the lock is released.
let''s try:
Original code block:
Line#44 delete
Line#46 Change:
这篇关于Form和Recordset关闭后无法删除Temp Table的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
