PRB: Find Methods Don't Use Indexes to Speed Up VB Data Access

- Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SYMPTOMS

The find methods take longer to find farther records than nearer records. For example, the FindFirst method takes longer to find records that are farther into the Dynaset or Snapshot. Using FindFirst, FindNext, or FindLast on indexed fields isn't any faster than on non-indexed fields.

CAUSE

Creating a Dynaset or Snapshot is relatively fast because of indexes. Indexes are used when you create the Dynaset or Snapshot, such as when you specify a selection criteria or an ORDER BY clause in the CreateDynaset method. However, after the Dynaset or Snapshot is created, it does not use the indexes for searches.

The find and move methods always search sequentially within the current Dynaset or Snapshot. The find and move methods don't use indexes. For example, the FindLast and MoveLast methods require reading the entire recordset before processing continues.

The find methods are: FindFirst, FindLast, FindNext, and FindPrevious. The move methods are: MoveFirst, MoveLast, MoveNext, and MovePrevious.

WORKAROUND

Repositioning the record pointer to a location far into a large Dynaset or Snapshot may be faster if you re-create the Dynaset or Snapshot instead of searching the existing one.

For example, you could change the RecordSource property of a data control at run time as follows:

   txtSearchString = "target string to find"
   ' Place the following two lines on one, single line:
   data1.Recordsource = "select * from mytable where myfield = '" &
      txtSearchString & "' order by myotherfield"
   data1.Refresh   ' Update the data control with the new Recordset

STATUS

This behavior is by design.

REFERENCES

• "Microsoft Visual Basic 3.0: Professional Features Book 2: Data Access Guide," Chapter 3. See the section "Positioning the Current Record in a Recordset."
• Help menu in Visual Basic.