Need help with ADODB Recordset.

[mytonytiger]

How hard is this to do?

I have a recordset in memory. I need to execute a SQL statement on it and generate a new recordset in the process. Is this possible WITHOUT having to first save the RS to a database.

what I need to do is create a RS with all records that contain duplicate entries within a particular field.

Any help would be greatly appreciated.
Thanks,
Tony

[Iouri]

I don't think that you can write an SQL against the recordset. You can execte an SQL based on original table or filter the existing recordset.

Here is an SQL to find duplicate records

TableX has 2 fields : FirstField and SecondField

SELECT DISTINCTROW First([FirstField]) AS [FirstField Field], First([SecondField]) AS [SecondField Field], Count([FirstField]) AS NumberOfDups
FROM TableX
GROUP BY [FirstField], [SecondField]
HAVING Count([FirstField])>1 And Count([SecondField])>1;

here are examples for filter

'apply filter
rs.Filter = "author like 'J*'" 'search field author which starts with J

'eliminate filter
rs.Filter = ""

rs.Filter = "fieldname = " & sCriteria



Iouri Boutchkine
iouri@hotsheet.com