强制 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.

为了避免这个问题，我想强制访问将所有行加载到内存中.通过反复试验，我发现访问 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屋！