W
worksfire1
How to speed up querying of 1 million records in a table? I know websites do
this all the time with their data. How else does one of a million bank
customers log in to their Bank of America account in a matter of a second (or
less!)
I want an employee to be able to type an account # in a text box on a Access
form then immediately see a list box pop up with all of the order #s
associated with the order specified. That seems to work perfectly and quick.
But then in my case, the employee selects/highlights one or several of the
order #s that pop up in the list box. Then the click "View Order Details"
and that is where things get really slow.
I have a piece of code that Dirk helped me write to shows a query that
contains the order details for all of the selected orders. It works, but
really slowly. We are talking several minutes to retrieve and dispaly the
Order details for the selected orders. What can I do to speed the retrieval
up when the users clicks the button to view the rental order details?
Private Sub cmdViewROEs_Click()
Dim strROEs As String
Dim varItem As Variant
With Me!lstROEs
If .ItemsSelected.Count = 0 Then
MsgBox "You have not selected any ROEs for display."
Else
' Make delimited list of orders.
For Each varItem In .ItemsSelected
' Note: this assumes that the field ROE is numeric.
' strROEs = strROEs & "," & .ItemData(varItem)
' If it's text, use this:
strROEs = strROEs & _
",'" & .ItemData(varItem) & "'"
Next varItem
' drop leading comma
strROEs = Mid$(strROEs, 2)
' Add an operator to the list.
If .ItemsSelected.Count > 1 Then
strROEs = "In (" & strROEs & ")"
Else
strROEs = "= " & strROEs
End If
' Update the SQL property of the query we'll
' use to display the orders.
CurrentDb.QueryDefs("qryDisplayROEsInfo").SQL = _
"SELECT * FROM orderdata WHERE [HeroRO] " & strROEs
' Open the query to display the orders.
'DoCmd.OpenQuery "qryDisplayROEsInfo"
End If
End With
End Sub
this all the time with their data. How else does one of a million bank
customers log in to their Bank of America account in a matter of a second (or
less!)
I want an employee to be able to type an account # in a text box on a Access
form then immediately see a list box pop up with all of the order #s
associated with the order specified. That seems to work perfectly and quick.
But then in my case, the employee selects/highlights one or several of the
order #s that pop up in the list box. Then the click "View Order Details"
and that is where things get really slow.
I have a piece of code that Dirk helped me write to shows a query that
contains the order details for all of the selected orders. It works, but
really slowly. We are talking several minutes to retrieve and dispaly the
Order details for the selected orders. What can I do to speed the retrieval
up when the users clicks the button to view the rental order details?
Private Sub cmdViewROEs_Click()
Dim strROEs As String
Dim varItem As Variant
With Me!lstROEs
If .ItemsSelected.Count = 0 Then
MsgBox "You have not selected any ROEs for display."
Else
' Make delimited list of orders.
For Each varItem In .ItemsSelected
' Note: this assumes that the field ROE is numeric.
' strROEs = strROEs & "," & .ItemData(varItem)
' If it's text, use this:
strROEs = strROEs & _
",'" & .ItemData(varItem) & "'"
Next varItem
' drop leading comma
strROEs = Mid$(strROEs, 2)
' Add an operator to the list.
If .ItemsSelected.Count > 1 Then
strROEs = "In (" & strROEs & ")"
Else
strROEs = "= " & strROEs
End If
' Update the SQL property of the query we'll
' use to display the orders.
CurrentDb.QueryDefs("qryDisplayROEsInfo").SQL = _
"SELECT * FROM orderdata WHERE [HeroRO] " & strROEs
' Open the query to display the orders.
'DoCmd.OpenQuery "qryDisplayROEsInfo"
End If
End With
End Sub