Filtering a Recordset

Filtering a Recordset

If you have a recordset already created and want to filter it by some criteria, you can use the Filter property of the recordset to do it. Just change the recordset’s Filter property to a WHERE clause and then set the recordset object to another recordset variable, like this:

 Dim objRS, objFilter ' As ADODB.Recordset Dim dcnDB ' As ADODB.Connection Set dcnDB = Server.CreateObject("ADODB.Connection") dcnDB.Open "Some connection string" Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT * FROM tblOrders", dcnDB, adOpenStatic objRS.Filter = "CustomerName Like %Smith%" Set objFilter = objRS 

Depending on how big the recordset is, you may want to simply do another query from the database. It will probably run faster, especially if you create a stored procedure to do the work for you. Also, make sure you have indexes on the fields that you’re using in your filters.


Share the Post: