为什么对 MS Access 列表框或组合框的重新查询会对数据库进行两次调用?

Question

我有一个包含多个列表框的表单。列表框具有来自直通查询的 SELECT 语句的行源，该直通查询针对 SQL 数据库运行 EXEC 语句。

由于性能问题，我们一直在使用 SQL Server 事件探查器监视服务器，并注意到当您运行 .重新查询 VBA 代码中的列表框，它实际上运行 TSQL EXEC 语句两次。我已经逐行遍历了 VBA 代码，并证明它是创建多个调用的一行代码 (listbox.requery(。

有没有人遇到过这个问题和/或有任何解决方案的想法。

为了进一步澄清， 列表框的行源设置为"SELECT * FROM qsptTestQuery"。 我有一个访问查询对象(例如名为qsptTestQuery(，它是一个返回行的传递查询。在 vba 代码中，我设置了 .此对象的 SQL 到 'Exec spTestProc 1234' 然后运行 listbox.requery。 当我运行 listbox.requery 代码行时，它会在探查器中触发 2 次调用。

Answer 1

一种解决方案是在 VBA 中自行调用传递查询，从而控制何时调用它。 列表框支持可以填充列表的自定义编写函数。 有关如何指定和编写函数的详细信息，请转到此处。

这可以让你更好地控制列表的人口，但我记得它不一定会删除重复的调用。 换句话说，您可能会发现它使用初始化代码多次调用此函数。 通过跟踪调用，您也许可以确定一种模式并编写缓存值的代码，并且仅在必要时重新执行传递查询。

下面是一个旧应用程序的示例，其中包含我的一些评论，我希望这些评论对编写您自己的评论有用。 我只需要使用一次(在很多很多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