Copy Filtered Recordset?

[Luis Figueira]

Hi,
I have a recordset (rs) and I want to copy some of the records to another
recordset like this:
'*************************************************************
'some code creating the rs Recordset
dim rs2 as ADODB.Recordset
rs.filter="FieldA='COND'"
set rs2 = rs
rs.filter = adFilterNone
'*************************************************************
But when I remove the filter the rs2 becomes equal to the nonfiltered rs!
I want th rs2 to only have the records from the rs.
How can I do this??
Thanks

[Carl Prothman]

Luis,
Try the following to "copy" a recordset. You can apply a filter before
making the copy.

This solution requires MDAC 2.5 (for the Stream object) with IE 5 (for XML
Save feature).

'*******
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim oStm As ADODB.Stream
Dim oRs2 As ADODB.Recordset

' Create and open a new Connection
Set oConn = New ADODB.Connection
oConn.Open "Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Initial Catalog=pubs;" & _
"User ID=sa;" & _
"Password=;"

' Create and open a new Recordset
Set oRs = New ADODB.Recordset
oRs.CursorLocation = adUseClient
oRs.Open "Select * From authors", oConn

' Save records into an ADO Stream
Set oStm = New ADODB.Stream
oStm.Open
oRs.Save oStm, adPersistXML

' Create a new Recordset based on the initial recordset (via Stream)
Set oRs2 = New ADODB.Recordset
oRs2.Open oStm
Debug.Print oRs2.RecordCount
'*******

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

"Luis Figueira" <luis.f...@getronics.com> wrote in message
news:058701c0a5aa$77486370$b1e62ecf@tkmsftngxa04...

[Sukesh Hoogan]

Hi Luis

This is what my view is on your question.
Recordset is created from the database table. Of course,you can have four
recordsets of table of different tpyes - Table type(default), Dynaset,
Snapshot and ForwardOnlyCiursor )
Naturally, once the filter is
removed it will contain the original recordset.. What you need to do is to
have another table in the database with separate ADO control and then copy
and update (by removing the records not meeting the condition of your
filter) this new table before you remove the filter. The new table then
should have records you desire.

Regards
Sukesh

"Luis Figueira" <luis.f...@getronics.com> wrote in message
news:058701c0a5aa$77486370$b1e62ecf@tkmsftngxa04...

[Dean Macken]

Luis,

i'm afraid Sukesh is mixing up DAO with ADO, and so his solution will not work
(even if you can make any sense of it).

Carls solution is a good one and will work, however i reequires writing the data
to disk, and then reading it, which, depending on how many records you are
working with may be slow.

the reason your code fails is because rs1 and rs2 are pointers to the same
recordset (read up on Byval and ByRef in the help - sorry if you new this)

you dont mention why you want a copy, but if it is for read only purposes try
this...

dim rs2 as ADODB.Recordset

set rs2 = rs.clone (AdLockReadOnly)

'NOTICE THE FILTER IS ON RS2 !!
rs2.filter="FieldA='COND'"

'do not turn off the filter on RS2 until you have done with it, or it will show
all data.

the clone method creates a clone of the recordset. this is still the same
recordset but you can set filters, sorts and bookmarks independantly of the
original recordset. beware though as any updates, adds or deletes made to either
recordset affect both.

.

[Carl Prothman]

"Dean Macken" <Dea...@shuttleworth-uk.co.uk> wrote

> Carls solution is a good one and will work, however i reequires writing
> the data to disk, and then reading it, which, depending on how many
> records you are working with may be slow.
>

Dean,
Look again at my sampe code. Nothing is being written to a file. I'm
writing the XML to an in-memory Stream object.

[Dean Macken]

Apologies carl. i just saw the Save method being called and assumed you were
lobbing it to disk in order to read it. i should look a little deeper.

--

.