-
Nov 23rd, 2020, 05:45 PM
#1
Thread Starter
Addicted Member
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
-
Nov 24th, 2020, 01:04 PM
#2
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.
-
Nov 24th, 2020, 07:27 PM
#3
Thread Starter
Addicted Member
Re: Filtered Recordset Structures
Originally Posted by dilettante
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
-
Nov 25th, 2020, 02:06 AM
#4
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>
-
Nov 25th, 2020, 06:52 AM
#5
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|