You can limit the number of records you select from a database by using a WHERE clause in your query. If the user is supplying the criteria for the WHERE clause, you can use a SELECT COUNT(*) statement with that WHERE clause to find out how many records will be returned, and then decide if you want prompt your user to limit the criteria they are using.
For example, the starting version of the BookSales sample application (JetBook
\Samples\BookSales\BookSales.vbp) loads books into the Books list box (on the BookList form) by returning all of the books, and then walking through the recordset using the FindFirst and FindNext methods to find the records that belong in the list box. Changing this to a WHERE clause as shown in the BookSalesOptimized sample application (JetBook\Samples\BookSalesOptimized\BookSalesOptimized.vbp) makes this 4.5 times faster, even on this small set of 100 records.
A sample SELECT statement with a WHERE clause looks like this:
SELECT au_id, au_lname, au_fname FROM authors WHERE state = 'CA' ORDER BY au_lname
This is a good way to structure the query if your application needs all three fields that are returned. It also illustrates the concept of limiting the number of returned records by specifying a WHERE clause. Note that the statement also orders the data before returning it by using an ORDER BY clause. Although Microsoft Jet does provide the ability to sort a recordset, for efficiency reasons, it’s always better to let the server do the sort.