Access Cookbook - Ken Getz [171]
Solution
There is a very simple VBA technique that forces the rows of a combo or list box to load all at once when you open the form. All you have to do is force the code behind the form to calculate the number of items in the list.
Load frmComboFast in 08-03.MDB. Click the down arrow of the top combo box and scroll to the bottom of the list. Access loads only part of the list each time you scroll, so it takes many attempts to get to the last items on the list. Now do the same with the second combo box. This time, you can scroll immediately to the last item on the list.
Discussion
The Load event procedure in frmComboFast forces the second combo box to load the entire list, by calling the ListCount property of the control:
Private Sub Form_Load( )
Dim lngCount As Long
lngCount = cboFast.ListCount
End Sub
To use this code on your form, simply change the name of the control from cboFast to the name of your combo or list box. You can handle multiple controls by reusing the lngCount variable to retrieve the ListCount property value for each combo or list box that you want to load.
The form in this example loads a bit slower than it would if you didn't use this technique, because load time is sacrificed in order to improve the performance of the second combo box. If you need to use combo boxes that have very long lists, this is a price that your users probably will be quite willing to pay.
8.4. Use Jet Engine Optimizations to Speed Up Queries
Problem
You've heard that the Jet database engine includes optimizations you can use to improve the performance of your queries. How do you create queries that use take advantage of these optimizations?
Solution
The Jet engine (the database engine built into Access) can execute certain types of queries dramatically faster that others, depending on how you construct the queries. This solution explains how this technology works and how you can take advantage of it. It also introduces a technique for timing the execution of queries.
Load the 08-04.MDB database. Open the qryOr1 query in design view. This query, which is shown in Figure 8-11, contains criteria on two fields, Menu# and Quantity. It returns all records from tblOrderDetailsNoIndexes where Quantity = 13 or where Menu# = 25. If you switch to SQL view, you'll see the following Where clause:
WHERE (((tblOrderDetailsNoIndexes.[Quantity])=13)) OR (((tblOrderDetailsNoIndexes.
[Menu#])=25))
Figure 8-11. The qryOr1 returns rows where Quantity = 13 or Menu# = 25
Close the query and open the tblOrderDetailsNoIndexes table to confirm that this table has no indexes. The qryOr2 and qryOr3 queries are identical to qryOr1, but they are based on different tables. qryOr2 is based on tblOrderDetailsPartialIndexed, which contains an index on the Menu# field, and qryOr3 is based on tblOrderDetailsFullyIndexed, which contains indexes for both Menu# and Quantity.
Run the three queries in turn. You should notice that qryOr3 is much faster than qryOr1 or qryOr2, which are of similar speed. To get more accurate timings, open the frmQueryTimer form in form view and create a new test comparing the three queries, as shown in Figure 8-12. Press the Run Test button to begin executing each query the number of times specified in the Number of Reps text box. When the test is complete, press the Results button to view a Totals query datasheet that summarizes the results of the test (see Figure 8-13). When we ran this particular test on a 650-MHz Pentium III machine with 448 MB of memory, qryOr3 was 3.67 times faster than qryOr2 and almost 60 times faster than qryOr1! On a slower machine, the results would be even more dramatic.
Figure 8-12. A test comparing three queries
Figure 8-13. The qryOr3 query is 60 times faster than qryOr1
Follow these steps to take advantage of query optimization in your own queries:
Index all table fields that are referenced in the