2

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

Heinzi
  • 167,459
  • 57
  • 363
  • 519

3 Answers3

2

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.

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.

AVG
  • 1,317
  • 8
  • 12
  • Nice idea, but it doesn't work. The locks still show until ListCount is called. Here's what I tried: `Set qdef = CurrentDb.CreateQueryDef("myQuery", "SELECT * FROM myTable") : qdef.Properties.Append qdef.CreateProperty("RecordsetType", dbByte, 2) : myListBox.RowSource = "myQuery"` (I also tried manually creating the query and setting its RecordsetType property - did not work either.) – Heinzi Dec 14 '15 at 13:11
  • Setting the recordsettype to snapshot won't prevent locks. Access will request and load all records in a single call, so it all happens much faster. If that time is so long that it affects your application, maybe you are populating the listbox with too many records. Just how many are there? Instead of select *, only select the necessary fields. That will also speed things up. Are there indexes on the table that support any where clause you are using? – AVG Dec 14 '15 at 13:28
  • 1
    Even when using a snapshot query, Access definitely does *not* load all records in a single call: I just tested it and waited for 1 min., and the locks were still there; after calling ListCount (finishes instantly), the locks were gone. – Heinzi Dec 14 '15 at 13:54
  • This is a minimal example to reliably reproduce the problem. In reality, I'm not using `SELECT *` and I'm filtering the table to those records which are actually required (which, apparently, is still "too much" for Access to load them). Anyway, since there is no documented "limit" for the number of records Access can load in a single batch, reducing the number of records would only reduce the *probability* of the problem occurring, it would not *reliably prevent* it. (I need the latter - our customers need software that works, not software that works "most of the time". ;-)) – Heinzi Dec 14 '15 at 13:57
  • When using a snapshot Access is supposed to make one call and get all of the records. That's what is indicated in https://msdn.microsoft.com/en-us/library/bb188204.aspx. I just did a text using SQL Profiler and you are correct. For a list or combo, even when the rowsource is a snapshot query, Access pulls in chunks. So, I guess you'll just have to get the listcount, which seems to do what you need. I haven't found any way to force an ODBC connection to pass query hints. Have you tried the option of a passthrough query or SQL Server view with the NOLOCK hint, as noted in my answer? – AVG Dec 14 '15 at 15:24
  • Thanks for the link; I agree, the documentation seems to be faulty here. We have used NOLOCK in the past to circumvent such issues, but stopped using it some years ago it since it [might return transactionally inconsistent data](http://stackoverflow.com/a/210227/87698). – Heinzi Dec 14 '15 at 16:26
1

I have used this method (accessing .ListCount) to avoid the following behaviour in Listboxes or Comboboxes with large Rowsources:

  • Access loads first part of rows
  • User wants to scroll to the end
  • Access loads more records, scroll bar jumps up
  • User has to scroll again
  • etc. etc. until finally all rows have been loaded

and found it to be quite reliable.

But unless you want to avoid creating an extra query for the listbox, the solution by AVG sounds cleaner.

Andre
  • 26,751
  • 7
  • 36
  • 80
0

Create a view on SQL server using the (NOLOCK). Then link to that view and use it as the SQL source for the listbox.

While you can add code, adopt recordsets etc, or even force the listbox to fill all records, such changes could occur in 100’s of places in a typical application. (too many potential changes).

A simple view with NOLOCK should suffice.

Eg:

SELECT * FROM tblContaacts WITH (NOLOCK) order by LastName
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • We have used NOLOCK in the past to circumvent such issues, but stopped using it some years ago it since it [might return transactionally inconsistent data](http://stackoverflow.com/a/210227/87698). – Heinzi Dec 15 '15 at 07:17
  • And this is going to be a problem for selecting one silly value from a combo box or listbox? Surely you jest! – Albert D. Kallal Dec 16 '15 at 16:45
  • Well, I want the multiple columns of my listboxes and comboboxes to show consistent values. With NOLOCK, this is not guaranteed. – Heinzi Dec 16 '15 at 19:34