asked on

copy ado recordset to recordset distinct

I have a disconnected recordset that I have loaded with data, I would like to sort and remove the duplicates based on a combination of 2 of the fields. I am thinking a query copying the data into a 2nd recordset using a distinct option but am not sure how to accomplish.

judgeking

You can create a clone of a recordset and filter it like this:

  Dim oRs As New ADODB.Recordset
  Dim oRsClone As ADODB.Recordset
  ...
  ...
  ...
  Set oRsClone = oRs.Clone
  oRsClone.Filter = "ID > 99 AND CreatedDate > 'Jan 1, 2012'"

Open in new window

ASKER CERTIFIED SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

judgeking

Dhaest, the author listed this question in the VB Classic zone. There are no rows or datatables.

cmrobertson

ASKER

clone is not quite what i want, that is still associated with the original recordset. I know in DAO I can open a recordset and fill from a table and use the "DISTINCT" option so it only returns distinct records based on the field. What I would like to know how to do if possible is to query my original recordset with perhaps a select statement returning the results into the 2nd recordset.

Dirk Haest

You can always use the following below

Public Function FilterRecordset(rsSrc As Recordset, sFilter As String) As Recordset
Dim rsClone As Recordset
Set rsClone = rsSrc.Clone
rsClone.Filter = sFilter
Set FilterRecordset = New Recordset
Set FilterRecordset.DataSource = rsClone
End Function

Public Function CloneRecordset(rsSrc As Recordset) As Recordset
With New PropertyBag
.WriteProperty "rs", rsSrc, Nothing
Set CloneRecordset = .ReadProperty("rs", Nothing)
End With
End Function

These work best on client-side ADO recordsets.