Create reocrdset from reocordset

[Jose Perdigao]

I create a recordset, now I would like create a recordset from the first one,
selecting only 3 fields ( the first one has 10 columns) and also I would like
to use a criteria.
is it possible?
I would like apply the same logic when we create a query and then we create
another query form the first one.

Any ideas?

thanks
JCP

[Tom van Stiphout]

I am assuming you have a really good reason to create this second recordset,
and not re-use the first one.
If your recordset is an ADO recordset object, you can create a new one out
of thin air (set rs2=New ADODB.Recordset), add 3 fields to it
(rs2.Fields.Append), and in a loop add the data to it (rs2.AddNew).
If it is a DAO recordset, you may have to requery the database.

-Tom.

"Jose Perdigao" <JosePe...@discussions.microsoft.com> wrote in message
news:B20D4F3B-99D7-4773...@microsoft.com...

[JCP]

Thanks Tom,

Tom, I leave here an example about some scenarios that I need.
Please, could you give me an example how can create sub recordset?

Thanks.

I will try to explain why I need to create recordset from a main recordset

Some times I need create a main recordset and then transfer this data to ms
excel where one sheet receive all data and I have another sheets
where I need only some records or only some columns.

I think if the main recordset is on the computer (front end) is faster if I
create recordset from the first one.

to copy the data to ms excel I'm using the method CopyFromRecordset
recordset. It works fine, I don't know if there is another method better than
this.

I give a simple example about the main recordset:

Dim cnn As ADODB.Connection
Dim rec As ADODB.recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
Set Rec = New ADODB.recordset

StrSql="SELECT dDate, Meter, Net, WatMeter FROM dbo.J1_OilProdYL WHERE
BlockID = N'block 2')"

Rec.Open strSQL, cnn

The recordset give the following data:

dDate Meter Net WatMeter
01-01-2007 Bagre 11117 5861
01-01-2007 Essungo 617 0
01-01-2007 Lombo 9281 5875
02-01-2007 Bagre 10985 5797
02-01-2007 Essungo 603 0
02-01-2007 Lombo 8813 5946

Sub recordset 1
Now I would like create a new recordset from main recordset (rec) to give
data only for the meter='Bagre'

dDate Meter Net WatMeter
01-01-2007 Bagre 11117 5861
02-01-2007 Bagre 10985 5797

Sub recordset 2
Also I would like to create a new sub recordset to give only the following
columns (reducing columns)

dDate Meter WatMeter
01-01-2007 Bagre 5861
01-01-2007 Essungo 0
01-01-2007 Lombo 5875
02-01-2007 Bagre 5797
02-01-2007 Essungo 0
02-01-2007 Lombo 5946

PS. the main recordset has many recordsets

Thanks
jcp
--
Jose

[Jamie Collins]

On Sep 25, 6:08 pm, JCP <J...@discussions.microsoft.com> wrote:
> I think if the main recordset is on the computer (front end) is faster if I
> create recordset from the first one.
>

To fabricate a recordset with a reduced number of columns, something
like this:

Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset

rs2.Fields.Append _
rec.Fields("dDate").Name, _
rec.Fields("dDate").Type, _
rec.Fields("dDate").DefinedSize
rs2.Fields.Append _
rec.Fields("Meter").Name, _
rec.Fields("Meter").Type, _
rec.Fields("Meter").DefinedSize

rs2.Fields.Append _
rec.Fields("WatMeter").Name, _
rec.Fields("WatMeter").Type, _
rec.Fields("WatMeter").DefinedSize
rs2.Open

Dim counter As Long
rec.MoveFirst
For counter = 0 To rec.RecordCount - 1
rs2.AddNew Array( _
rec.Fields("dDate").Name, _
rec.Fields("Meter").Name, _
rec.Fields("WatMeter").Name), _
Array( _
rec.Fields("dDate").value, _
rec.Fields("Meter").value, _
rec.Fields("WatMeter").value)
rec.MoveNext
Next

rs2.MoveFirst
MsgBox rs2.GetString

For the reduced number of rows you could simply use

rec.Filter = "meter = 'Bagre'"

before transferring the data.

FWIW fabricating seems like a lot of work (i.e. processing time and
coding time) just to be able to use CopyFromRecordset. Take a look at
the Recordset object's GetRows property to return an array where you
can specify the fields to be returned. You need to be able to
'transpose' the array (columns to rows and vice versa) to be able to
read into an Excel Range; Excel has a worksheet function for this but
it has limits (check the Excel Help) e.g. something like:

Sheet1.Range("A1:C2").Resize(rec.RecordCount).Value = _
Excel.Application.WorksheetFunction.Transpose(rec.GetRows(, ,
Array("dDate", "Net", "WatMeter")))

> PS. the main recordset has many recordsets

Then three database roundtrips to produce three different recordsets
may be faster than all this procedural processing in the middleware
and front end, especially so if you are fabricating recordsets.

Jamie.

--

[Jose Perdigao]

Question:
Do you have any idea how can i copy recordset filter to excel?
About getrows, I read in msdn web site the following untile excel 97 we
using getrows, after we use xlBook.Sheets(1).Range("D10").CopyFromRecordset.

I'm trying to do something like this but doesn't work, it copy all records
to excel spreadsheet, it means, it ignores the filter.

Dim cnn as ADODB.connection
Dim rs as ADODB.recordset
Dim rs1 as ADODB.recordset

' Code to open a specifive excel file ...

Set cnn= CurrentProject.Connection
Set rs= New ADODB.recordset
rs.Open strSQL, cnn
'debug.print rs1.recordcount=62

rs.filter="Source='Bagre'" 'This recordset has 7 records

xlBook.Sheets(1).Range("D10").CopyFromRecordset rs

'The line above copy all data from the recordset, I mean, copy into excel
62 records and not 7. It ignore the filter

'I tried to use:

Set cnn= CurrentProject.Connection
Set rs= New ADODB.recordset
rs.Open strSQL, cnn
'

rs.filter="Source='Bagre'"
Set rs1=rs 'this recordset has 7 rows but when I use CopyFromRecordset to
copy data from recordset to escel, it copy all records.
xlBook.Sheets(1).Range("D10").CopyFromRecordset rs1

'Also the line above copy 62 records into excel and not 7. It ingore the
filter

[Jamie Collins]

On Sep 29, 3:14 pm, Jose Perdigao

<JosePerdi...@discussions.microsoft.com> wrote:
> Question:
> Do you have any idea how can i copy recordset filter to excel?
> About getrows, I read in msdn web site the following untile excel 97 we
> using getrows, after we use xlBook.Sheets(1).Range("D10").CopyFromRecordset.
>
> I'm trying to do something like this but doesn't work, it copy all records
> to excel spreadsheet, it means, it ignores the filter.

Are *you* running Excel97? For me running Excel 2007, Excel's
CopyFromRecordset method honours the Filter. I tested using both
client- and server-side cursors.

You could use the ADO Recordset's GetRows method to create an array
then set your Excel Range's Value property to equal the array, as I
demonstrated in my earlier post.

Jamie.

--