VB Forum / Database Access / July 2006

Thanks for the reply...

I don't have any problem using joins (inner/outer) when retrieving data from
the database although I try to create these as parameterized queries saved
in the DB.

This issue is this...

I was under the impression that ADO tools provided a way to filter a local
recordset locally a second time for one or more conditions without going
back to the database itself. I already do this using arrays (which seem to
work very fast) but I thought it might be cleaner to do it directly with the
recordsets.

Actually there is another way that is almost infinitely easier to code, and
is faster as well:

1. Filter recordset to desired subset
2. Persist the filtered recordset to an XML object
3. Open new recordset using XML object as source

' rs is assumed to be an open ADODB.Recordset
'
Dim xml As MSXML2.DOMDocument, rsSubset as ADODB.Recordset
Set xml = New MSXML2.DOMDocument
Set rsSubset = New ADODB.Recordset

rs.Filter = "[your conditions]"
rs.Save xml, adPersistXML ' save to IStream exposed by xml object
rsSubset.Open xml

As opened above, rsSubset is entirely disconnected from rs and its
underlying data.  It can, however, be reconnected, by setting
rsSubset.ActiveConnection.

To combine rows from two recordsets into one, let's assume that xmlDest was
created using the technique above, to include the subset of rows from one of
the source recordsets, and xmlSrc contains the rows from the other source
recordset, that are to be combined.

Dim DestNode As IXMLDOMNode, SrcNode As IXMLDOMNode
Dim SrcNodeList As IXMLDOMNodeList

Set DestNode = xmlDest.selectSingleNode("xml/rs:data")
Set SrcNodeList = xmlSrc.selectNodes("xml/rs:data/z:row")
For Each SrcNode in SrcNodeList
   DestNode.appendChild SrcNode.cloneNode(True)
Next
rsCombined.Open xmlDest

In most cases (read: all cases I have observed), moving rows as XML nodes is
appreciably faster than adding rows to a recordset object, and iterating
fields to copy rows from another -- in part I'm sure to all of the variant
overhead that's skipped.

Note that these source fragments are by no means all-inclusive -- for one
thing, if any inserts/updates/deletes have been performed on a source
recordset, the rows involved will be ignored by the node paths used above.
But if you're interested in manipulating persisted recordsets at the xml
level, it's a fair jumping-in point, at least.

-Mark