访问SQL Server插入被选择阻止 [英] Access SQL Server Insert blocked by Select

问题描述

好的，我们有一个多用户(25个用户)访问2013 FE和一个SQL Server 2012 BE.直到昨天为止，整个系统都可以正常运行，现在已经完全停止.

OK we have a multi user (25 users) Access 2013 FE and a SQL Server 2012 BE. Up until yesterday the whole system was working FINE and now it has completely stopped.

• 如果用户A通过从表Z读取的直接选择查询打开了一条记录，则如果用户B尝试在表Z上进行插入，则他们会收到超时消息.当我转到SQL Server并运行SP_WH02时，它指出用户B被用户A阻止.然后，当我调查阻止用户B的命令时，它只是一个简单的SELECT语句.

有人知道为什么会这样吗?

Does anyone know why this would be?

用户A打开的表单具有记录锁定=无锁定且记录集类型=动态集"

The form that User A has open has Record Locks = No Locks and Recordset Type = Dynaset

记录源是一个SELECT，它检索两个字段，其中键字段是基于另一个字段值的参数.

The record source is a SELECT, retrieving two fields where the key field is a parameter based on the value of another.

但是，这个系统几个月来没有任何变化，所以我对为什么会发生这种情况感到困惑.

However, nothing has changed on this system for months, so I'm confused as to why this would happen.

感谢您的帮助.

推荐答案

与

It may be the same or similar issue as in MS Access holds locks on table rows indefinitely

访问仅获取大记录源的前x行，使表处于ASYNC_NETWORK_IO等待状态，即处于锁定状态.

Access only fetches the first x rows of the big recordsource, leaving the table in a ASYNC_NETWORK_IO wait state, i.e. locked.

可能的解决方案是:

• 没有选择所有记录的表单或查询.滚动浏览超过20k条记录通常没有太大意义.
• 强制访问以获取所有记录，以释放锁.您可以使用Me.RecordsetClone.MoveLast完成此操作，例如在Form_Load()中.仅建议使用快速网络连接.

• Don't have forms or queries that select all records. It usually doesn't make too much sense to scroll through 20k+ records.
• Force Access to fetch all records, to release the lock. You can do this with Me.RecordsetClone.MoveLast e.g. in Form_Load(). Only advisable with a fast network connection.

这篇关于访问SQL Server插入被选择阻止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！