Force MS Access to retrieve all ListBox rows and release locks

Question

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.

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:
'
' RequestMode ResourceType ObjectName IndexName           
' -------------------------------------------------------
' IS          OBJECT       myTable                       
' S           KEY          myTable    PK__myTable__17C...
' IS          PAGE         myTable    PK__myTable__17C...

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.)

Answer 1

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.)

Hi Heinzi,

I cannot help with the ListBox as such, because I do not use them. I can imagine that asking for ListCount is comparable to MoveLast in RecordSets.

Instead of ListBoxes I use "regular" forms with with a RecordSource that is comparable with the RowSource of ListBoxes. In a couple of cases I must select e.g. a Person from a Person_tbl with more than 100.000 records, with all comparable Lastnames ans Firstnames. To select the right Person I first present a pre-selection form, asking for instance the first character of the Lastname and the first character of the Firstname. With these values the RecordSource is narrowed down, so that the number of records is reduced considerably. In fact I use a selection form where the user can choose almost any condition on any field.

The same you could do for the RowSource of the ListBox. When the ListBox gets the focus, ask the first one or the first two characters, and modify the Where-part of rhe RowSource accordingly.

Imb.

Answer 2

I cannot help with the ListBox as such, because I do not use them. I can imagine that asking for ListCount is comparable to MoveLast in RecordSets.

That's also my assumption.

The same you could do for the RowSource of the ListBox. When the ListBox gets the focus, ask the first one or the first two characters, and modify the Where-part of rhe RowSource accordingly.

That's a good idea, but, unfortunately, the data is already filtered to the amount required for the business need. Anyway, since there is no documented "limit" on how many rows one can display before this problem occurs (or is there?), this would only reduce the probability of the problem happening, rather than preventing it reliably.

Best regards
Heinzi

Answer 3

Accessing the .ListCount property is well known as "the way" to get all the rows to load, and it is what I do.  I don't know for sure whether that releases all locks on the table.  I'm guessing it does, but you would have to test that.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html