Почему запрос в списке доступа MS или поле со списком делает два вызова базы данных?

Question

У меня есть форма с несколькими списками. В списках есть источник строки оператора SELECT из промежуточного запроса, который выполняет оператор EXEC для базы данных SQL.

Из-за проблем с производительностью мы наблюдали за сервером с помощью SQL Server Profiler и заметили, что когда вы запускаете.Requery для списка в коде VBA, он фактически выполняет оператор TSQL EXEC дважды. Я шаг за шагом прошел по коду VBA и доказал, что это одна строка кода (listbox.requery), которая создает множественные вызовы.

Кто-нибудь сталкивался с этим и / или получил какие-либо идеи решения.

Для дальнейшего пояснения источник строк в окне списка установлен на "SELECT * FROM qsptTestQuery". У меня есть объект запроса Access (названный, например, qsptTestQuery), который представляет собой сквозной запрос, который возвращает строки. в коде vba я устанавливаю.SQL этого объекта в 'Exec spTestProc 1234' и затем запускаю listbox.requery. когда я запускаю строку кода listbox.requery, он запускает 2 вызова в профилировщике.

Answer 1

Одним из решений является вызов сквозного запроса самостоятельно в VBA, что дает вам контроль над тем, когда он вызывается. ListBox поддерживает написанную пользователем функцию, которая может заполнять список. Перейдите сюда для получения подробной информации о том, как указать и написать функцию.

Это может дать вам более точный контроль над заполнением списка, но, насколько я помню, это не обязательно удаляет повторяющиеся вызовы. Другими словами, вы можете обнаружить, что он вызывает эту функцию, умноженную на несколько раз с кодом инициализации. Отслеживая вызовы, вы, возможно, сможете определить шаблон и написать код, который кэширует значения и повторно выполняет только сквозной запрос, когда это необходимо.

Ниже приведен пример из старого приложения, в котором содержатся некоторые мои комментарии, которые, я надеюсь, будут полезны при написании ваших собственных. Мне приходилось использовать это только один раз (из многих, многих элементов управления ListBox и ComboBox), но это работало хорошо.

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