强制MS Access检索所有ListBox行并释放锁 [英] Force MS Access to retrieve all ListBox rows and release locks

问题描述

我有一个列表框，其中的RowSource绑定到附加的SQL Server表.如果表很大，Access不会加载完整的结果集，而是创建一个服务器端游标并按需"加载数据，因为列表框向下滚动.这是一个很好的功能，因为它允许列表框和组合框快速显示结果.

I have a listbox with a RowSource bound to an attached SQL Server table. If the table is large, Access does not load the complete result set but rather creates a server-side cursor and loads the data "on demand", as the list box is scrolled down. This is a nice feature, since it allows list boxes and combo boxes to show results fast.

但是，这会在表上创建一个共享锁，即，只有具有列表框的用户一直向下滚动并释放该锁，其他用户才能插入新行.这是已知问题.

However, this creates a shared lock on the table, i.e., no other user can insert new rows until the user with the list box has scrolled all the way down and the lock is released. This is a known problem.

为避免此问题，我想通过 force Access将所有行加载到内存中.通过反复试验，我发现访问 ListCount 属性似乎可以做到这一点:

To avoid this issue, I want to force Access to load all the rows into memory. By trial-and-error, I have found that accessing the ListCount property seems to do exactly that:

myListBox.RowSource = "myTable"

' There are now shared locks on the table in SQL Server:
'
' ResourceType ObjectName IndexName           RequestMode
' -------------------------------------------------------
' OBJECT       myTable                        IS
' KEY          myTable    PK__myTable__17C... S
' PAGE         myTable    PK__myTable__17C... IS

someDummyVariable = myListBox.ListCount

' The locks are now gone!

这种方法有多可靠?如果不是，是否有可靠的方法?

How reliable is this method? If it isn't, is there a reliable method?

(我知道一些变通方法，例如将数据复制到临时表或创建值列表，但如果可能的话，我宁愿避免这种情况.)

(I know about workarounds such as copying the data to a temporary table or creating a value list, but I'd rather avoid that, if possible.)

推荐答案

不是将表用作行源，而是创建查询并将查询的recordsettype属性设置为快照.然后，Access应该在一次调用中获得所有记录.

Instead of using the table as the rowsource, create a query and set the query's recordsettype property to snapshot. Access should then get all of the records in one call.

您还可以尝试在SQL Server中创建视图并包括NOLOCK提示，或者创建直通查询并在SQL中使用NOLOCK提示.

You could also try creating a view in SQL Server and include the NOLOCK hint or create a passthrough query and use the NOLOCK hint in the SQL.

这篇关于强制MS Access检索所有ListBox行并释放锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！