Results 1 to 5 of 5

Thread: Filtered Recordset Structures

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    168

    Question Filtered Recordset Structures

    In a recordset that has been filtered, does the filtered recordset (object?) still have the data (that has been filtered "out") in it?


    For example:

    In a recordset that has records 1, 2, 3, 4, & 5 in it, and has had a filter applied to exclude records 1 and 4, if I clone the filtered recordset, will the cloned recordset have only the data elements of records 2, 3 &5 of the filtered original, where:

    Record 1 of the "new" recordset holds the data of the original record 2,
    Record 2 of the "new" recordset holds the data of the original record 3,
    Record 3 of the "new" recordset holds the data of the original record 4,


    Thoughts? Still trying to learn

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Filtered Recordset Structures

    It isn't as simple as that.

    In the degenerate case of the most expensive (client-side cursor) Recordset that's true. But in server-side cursor Recordsets there is a lot more going on.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    168

    Re: Filtered Recordset Structures

    Quote Originally Posted by dilettante View Post
    It isn't as simple as that.

    In the degenerate case of the most expensive (client-side cursor) Recordset that's true. But in server-side cursor Recordsets there is a lot more going on.
    Thanks for getting back on this. I no have enough rope to hang myself with... ie it give me a good starting point for further inquiry

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Filtered Recordset Structures

    > a filter applied to exclude records 1 and 4

    Is this something like rs.Filter = "ID <> 1 AND ID <> 4"?

    > if I clone the filtered recordset

    Is this something like Set rs2 = rs.Clone?

    For client-side recordsets both original and cloned recordset share the same data so changes to data made through the first instance are visible in the second one.

    Both instances have separate Filter property which does not get cloned (same for Sort property), i.e. rs2 will have empty Filter and will see records 1 and 4.

    It is possible to make a clone with empty filter that do *not* see filtered out records by using DataSource property like this

    Code:
    rs.Filter = "..." '--- cannot be an array of bookmarks
    Set rs2 = New Recordset
    rs2.CursorLocation = adUseClient
    Set rs2.DataSource = rs
    Browsing this synthetic client-side recordset with MoveNext/MovePrev would have abysmal performance though. This trick does not work with rs.Filter being an array of bookmarks too (for no apparent reason).

    Check out this InitRecordsetClone helper function that I'm using regularly

    Code:
    Option Explicit
    
    Public Function InitRecordsetClone( _
                rsSrc As Recordset, _
                Optional Filter As Variant, _
                Optional Sort As String, _
                Optional RetVal As Recordset) As Recordset
        Const FUNC_NAME     As String = "InitRecordsetClone"
        Dim rsClone         As Recordset
        
        On Error GoTo EH
        If Not rsSrc Is Nothing Then
            If Not IsMissing(Filter) Or IsArray(Filter) Then
                Set rsClone = rsSrc.Clone
                '--- note: Sort removes Filter so first set Sort
                rsClone.Sort = Sort
                rsClone.Filter = Filter
                If IsArray(Filter) Then
                    Set RetVal = rsClone
                Else
                    Set RetVal = New Recordset
                    RetVal.CursorLocation = adUseClient
                    Set RetVal.DataSource = rsClone
                End If
            Else
                Set RetVal = rsSrc.Clone
                RetVal.Sort = Sort
            End If
        Else
            Set RetVal = Nothing
        End If
        Set InitRecordsetClone = RetVal
        Exit Function
    EH:
        Err.Raise Err.Number, "InitRecordsetClone" & vbCrLf & Err.Source, Err.Description
    End Function
    
    Private Sub Form_Load()
        Dim rs          As Recordset
        Dim rs2         As Recordset
        
        Set rs = New Recordset
        rs.Fields.Append "ID", adInteger
        rs.Open
        rs.AddNew Array(0), Array(1)
        rs.AddNew Array(0), Array(2)
        rs.AddNew Array(0), Array(3)
        rs.AddNew Array(0), Array(4)
        
        Set rs2 = InitRecordsetClone(rs, "ID <> 1 AND ID <> 4")
        Debug.Assert rs2.RecordCount = 2 And rs2.Filter = adFilterNone
    End Sub
    cheers,
    </wqw>

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    168

    Re: Filtered Recordset Structures

    Both instances have separate Filter property which does not get cloned (same for Sort property), i.e. rs2 will have empty Filter and will see records 1 and 4
    .



    I was taking that "rs2" (my clone) from a rsSrc that itself was a filtered rs, and assigning its elements into an array used in a MSChart array, and so that is why I was getting bad results. It was as if the clone had ALL the records of rsSrc, just as If I have been assigning from an unfiltered rsSrc in the first place. [Which is zaxtly what you be saying]

    Looks like from your cool function that if for some reason you wanted to run a secondary filter pass on your functions rs2, you could do that and even make a rs3 having even less records.

    The reason I was on this trail is because I was taking an already created rs that had been filtered, and was extracting data elements from it to a MCChart array and things were all over the place- as if all the data in the clone was all the data in the source.

    So glad we have this forum and all the folks that are so very "up" on so many things!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width