recordset filter instead of re-opening recordset

[stormin_norm]

I searched all over for a good example of filtering a recordset then refiltering and want the most efficient way of handling the following:

I have query returning all people as
level name
10 smith
20 jones
20 jackson
40 simpson

so my db.OpenRecordSet ("qryAllPeople") will give me everyone. And then I simply want to apply a filter for each level as:

for lvl 10 to 90 step 10
rs.filter "[level]=" & lvl
do until rs.EOF
yadayada
rs.MoveNext
Loop

Right now, I constantly close and re-open the querydef in the for loop:

for lvl 10 to 90 step 10
set rs = db.OpenRecordSet ("Select * from qryAllPeople where level = "&lvl)
do until rs.EOF
yadayada
rs.MoveNext
rs.close
Loop

Would a bookmark be better or clone? I feel opening and closing the recordset is inefficient.

thanks!!

 

[MarkK]

You could do this ...

  set rs = db.OpenRecordSet( _
   "SELECT * FROM qryAllPeople WHERE level Mod 10 = 0")
  With rst
    Do While Not .eof
      yadayada
      rs.MoveNext
    Loop
    .close
  End With

The best option is typically to open exactly the recordset you need.

 

[stormin_norm]

THANKS! Sounds like 10 open queries it is.

I wonder if maybe bookmark with filtered set of 10 then do processing, go to top of loop and bookmark a filtered set of 20 then do processing.

Mmm. I think in ADO you can do something like this with seek or find.

 

[Galaxiom]

I know from experience that it is much, much, much faster to open the recordset multiple times with different criteria than to Filter or Find on a large recordset.

 

[MarkK]

My post wasn't clear enough. Your code opens a recordset with Level = 10, and loops through all the records, then opens a recordset with level = 20 and loops through all the records, then opens a recordset with level = 30, and so on.

My code opens only one recordset with level = 10 OR level = 20 OR level = 30, and so on, and loops through all the records. Notice the where clause that uses the Mod operator to returns all records where level is evenly divisible by 10, which is what your process seems to be.

Best option, if possible--and demonstrated above--open a single recordset with exactly the data you need. Second best option, open multiple recordsets for sub-groups of exactly the data you need. Worst option, open too big a recordset and filter, find or seek.

 

[stormin_norm]

Thanks guys! I do like the MOD version. Something an oreilly book would use

Speed is always good, so I'll use the multiple open vs. Filtering the recordset.

FYI- Found this good example on DAO filtering of recordsets:
http://msdn.microsoft.com/en-us/library/ff837300.aspx