Clash Royale CLAN TAG#URR8PPP

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

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.

1 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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.