If you are insane enough to have more than 500 fields in your recordset, then watch out for this annoying little bug. If you attempt to set a filter on a field with an ordinal position greater than 500, ADO will fail with "Unspecified error" (our favorite kind).

This is the first bug discovered by vbRad.com that will appear as an article on the Microsoft Knowledge Base. Yoo Hoo. Yes, our lives are completely pathetic.

Below is an example of code that will demonstrate the problem. It creates a disconnected recordset with a 1000 fields (from 'f1' to 'f1000'), adds 10 records and then tries to set the filter on each one. The code will fail when trying to set a filter on 'f502' (ordinal position 501).

In my code, I've created a workaround that may or may not work for you. If your recordset is disconnected, you can recreate (basically clone) this recordset into another, but with the field that needs filtering placed first. Then you could proceed with filtering as if no bug existed. If your recordset is not disconnected, then you are on your own.

1. Createa new project.
2. Add ADO 2.5 to the project
3. Add a command button

Private Sub Command1_Click()
    Dim ors As New ADODB.Recordset
    Dim x As Long, i As Long
    
    For x = 1 To 1000
        ors.Fields.Append "f" & x, adInteger, 0, adFldIsNullable Or adFldUpdatable
    Next
    ors.Open
    
    For x = 1 To 1000
        ors.AddNew
        For i = 0 To ors.Fields.Count - 1
            ors.Fields(i).Value = 100
        Next
        ors.Update
    Next
	
    ors.MoveFirst
    
    For x = 1 To 1000
        'set a filter - the error will happen when x=502
        ors.Filter = "f" & x & " > 250"	
        ors.Filter = ""
    Next	
End Sub