Why does a requery on an MS Access listbox or combobox make two calls to database?

Question

I have a form with multiple listboxes. The listboxes have a row source of a SELECT statement from a passthrough query which runs an EXEC statement against a SQL database.

Due to performance issues we have been monitoring the server with SQL Server Profiler and noticed that when you run .Requery on a listbox in the VBA code it actually runs the TSQL EXEC statement twice. I have stepped through the VBA code line by line and proved that it is the one line of code (listbox.requery) that creates the multiple calls.

Has anybody come across this and/or got any ideas of a solution.

For further clarification, The rowsource of the list box is set to 'SELECT * FROM qsptTestQuery'. I have an Access query object (named qsptTestQuery for example) which is a pass through query that returns rows. in the vba Code I set the .SQL of this object to 'Exec spTestProc 1234' and then run listbox.requery. when I run the listbox.requery line of code it triggers 2 calls in the profiler.

Answer 1

One solution is to call the pass-through query yourself in VBA, thereby giving you control over when it is called. The ListBox supports a custom-written function that can populate the list. Go here for details on how to specify and write the function.

This can give you finer control over population of the list, but as I recall it does not necessarily remove repetitive calls. In other words, you may find that it calls this functions multiples times with the initialization code. By tracing the calls, you can perhaps determine a pattern and write code which caches the values and only re-executes the pass-through query when necessary.

The following is an example from an old application and contains some of my comments that I hope are useful in writing your own. I've only ever had to do use this one time (out of many, many ListBox and ComboBox controls), but it worked well.

Private Function ListBoxResult(Ctr As Control, ID As Variant, Row As Variant, Col As Variant, code As Variant) As Variant
  '* listActionItems.[Row Source Type]
  '* Called to populate listActionItems

  '* PARAMETERS:
  '* Ctr:  A control variable that refers to the list box or combo box being filled.
  '* Id:   A unique value that identifies the control being filled. This is useful when you want to use the same user-defined function for more than one list box or combo box and must distinguish between them. (The example sets this variable to the value of the Timer function.)
  '* Row:  The row being filled (zero-based).
  '* Col:  The column being filled (zero-based).
  '* Code: An intrinsic constant that specifies the kind of information being requested.

  '* https://msdn.microsoft.com/en-us/library/office/ff845731.aspx
  '* Microsoft Access calls your user-defined function once for acLBInitialize, acLBOpen, acLBGetRowCount, and acLBGetColumnCount. It initializes the user-defined function, opens the query, and determines the number of rows and columns.
  '* Microsoft Access calls your user-defined function twice for acLBGetColumnWidth — once to determine the total width of the list box or combo box and a second time to set the column width.
  '* The number of times your user-defined function is called for acLBGetValue and acLBGetFormat to get list entries and to format strings varies depending on the number of entries, the user's scrolling, and other factors.
  '* Microsoft Access calls the user-defined function for acLBEnd when the form is closed or each time the list box or combo box is queried.
  '* Whenever a particular value (such as the number of columns) is required, returning Null or any invalid value causes Microsoft Access to stop calling the user-defined function with that code.

  Static days As Integer
  Static dt As Date
  Static result As Variant

  Select Case code
    Case acLBInitialize '0
      '* Return Nonzero if the function can fill the list; False (0) or Null otherwise.

      result = True
    Case acLBOpen '1
      '* Return Nonzero ID value if the function can fill the list; False or Null otherwise.
      result = True

    Case acLBGetRowCount '3
      If rsTemplateActions Is Nothing Then
        result = 0
      Else
        On Error Resume Next
        result = rsTemplateActions.RecordCount
        If Err.number <> 0 Then
          result = 0
        End If
      End If

    Case acLBGetColumnCount '4
      '* Columns: Action Type, Scheduled Date, Description, Priority
      result = 5

    Case acLBGetColumnWidth '5
      '* 1440 is twips per inch; -1 is default
      Select Case Col
        Case 0: result = 1.5 * 1440
        Case 1: result = 0.8 * 1440
        Case 2: result = 1# * 1440
        Case 3: result = 1.8 * 1440
        Case 4: result = 0.6 * 1440
        Case Else
          result = -1
      End Select

    Case acLBGetValue '6
      result = "-"

      If Not rsTemplateActions Is Nothing Then
        On Error Resume Next
        rsTemplateActions.MoveFirst
        If Err.number = 0 Then
          If Row > 0 Then rsTemplateActions.Move Row

          Select Case Col
            Case 0 'Action Type
              result = rsTemplateActions![Type Text]
            Case 1
              days = 0
              If IsNumeric(rsTemplateActions![DaysAdded]) Then
                days = rsTemplateActions![DaysAdded]
                result = "+" & days & " days"
              Else
                result = "?"
              End If
            Case 2 'Scheduled Date
              days = 0
              If IsNumeric(rsTemplateActions![DaysAdded]) Then
                days = rsTemplateActions![DaysAdded]
              End If
              If IsDate(txtActionDate.value) Then
                dt = CDate(txtActionDate.value)
                dt = DateAdd("d", days, dt)
                result = Format(dt, "mm/dd/yyyy")
              Else
                result = "?"
              End If
              Err.Clear
            Case 3 'Descrip
              result = rsTemplateActions!Description
            Case 4 'Priority
              result = ActionPriority(rsTemplateActions!Priority)
          End Select
        End If
      End If

    Case acLBGetFormat '7
      '* 1440 is twips per inch
      Select Case Col
        Case Else
          result = -1 'Default format
      End Select

    Case acLBEnd '9
      '* Only called when form is closed, not for each requery (I suppose for closing resources)
      'On Error Resume Next

    'Case acLBClose '8
    '  'NOT USED according to online resources.
    'Case Else
    '  Debug.Print "ListBoxResult Code = " & Code
  End Select

  ListBoxResult = result

End Function