Using SQL Against Resultset

[Faina]

Does anybody know if I can open a resultset and have a resultset as a data
source? For example:

I open first resultset as
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Open "SELECT * from psivert;"

Then I would like to create a second resultset against rs that was just created.If
this is possible can I see the code sample
please?

The reason I need to open a second recordset against first recordset is described
below. If there are any other ideas on how to handle it please let me know.

I have to write an application that needs to recalculate spread
control on the screen at the same speed Excel does it(immediately), save
changes to the local Access 97 database and
refresh a second screen(2 screen has to be open at the same time)
to reflect changes from the first screen. First screen is a
detail level screen and 2nd screen is a summary screen.
If I can do all changes against resultsets in memory instead of DB it should
be much faster(I think). Also in order to
do what I need to do I should be able to run different SQL functions against
resultset in memory(sum, group by etc). Then
at predetermined time intervals I will save these resultsetsets
to the Access DB.

Thanks for any suggestions

[Andrew Grillage]

You cannot get a recordset from a recordset. The best solution is to have
multiple recordsets for what you want to do.

--

Andrew Grillage
http://www.concresco.com


Faina <Flekakh@kraft.com> wrote in message news:3903c4ef$1@news.devx.com...
>
> Does anybody know if I can open a resultset and have a resultset as a data
> source? For example:
>
> I open first resultset as
> rs.CursorType = adOpenKeyset
> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient
> rs.Open "SELECT * from psivert;"
>
> Then I would like to create a second resultset against rs that was just
created.If
> this is possible can I see the code sample
> please?
>
> The reason I need to open a second recordset against first recordset is
described
> below. If there are any other ideas on how to handle it please let me
know.
>
> I have to write an application that needs to recalculate spread
> control on the screen at the same speed Excel does it(immediately), save
> changes to the local Access 97 database and
> refresh a second screen(2 screen has to be open at the same time)
> to reflect changes from the first screen. First screen is a
> detail level screen and 2nd screen is a summary screen.
> If I can do all changes against resultsets in memory instead of DB it
should
> be much faster(I think). Also in order to
> do what I need to do I should be able to run different SQL functions
against
> resultset in memory(sum, group by etc). Then
> at predetermined time intervals I will save these resultsetsets
> to the Access DB.
>
> Thanks for any suggestions

[Paul Dionne]

This idea has come up a few times here in the newsgroups and nobody has come
up with any sort of recommendation on how to do it.

Work around ideas have been 1) FILTER, 2) creating a second recordset based
on the same source as the original 3) looping through the recordset and
writing the values you want to a second recordset (or somewhere else) 4)
nested queries and 5) write the recordset to a temporary table and query
that table

Paul

Faina wrote in message <3903c4ef$1@news.devx.com>...
>
>Does anybody know if I can open a resultset and have a resultset as a data
>source? For example:

[Kamran Mahbub]

Of course that is not possible but you can programmatically create an ADO
recordset (or load the main recordset w/o any records if you need it to be
empty) or CLONE the main recordset as many times as you need. Then you can
use these copies of the rs for calculations or to save them locally. And
it does not matter even if you change the data in these clones (for temporary
use) as far as they are disconnected.

Kamran


"Faina" <Flekakh@kraft.com> wrote:
>
>Does anybody know if I can open a resultset and have a resultset as a data
>source? For example:
>
>I open first resultset as
> rs.CursorType = adOpenKeyset
> rs.LockType = adLockOptimistic
> rs.CursorLocation = adUseClient
> rs.Open "SELECT * from psivert;"
>
>Then I would like to create a second resultset against rs that was just
created.If
>this is possible can I see the code sample
>please?
>
>The reason I need to open a second recordset against first recordset is
described
>below. If there are any other ideas on how to handle it please let me know.
>
>I have to write an application that needs to recalculate spread
>control on the screen at the same speed Excel does it(immediately), save
>changes to the local Access 97 database and
>refresh a second screen(2 screen has to be open at the same time)
>to reflect changes from the first screen. First screen is a
>detail level screen and 2nd screen is a summary screen.
>If I can do all changes against resultsets in memory instead of DB it should
>be much faster(I think). Also in order to
>do what I need to do I should be able to run different SQL functions against
>resultset in memory(sum, group by etc). Then
>at predetermined time intervals I will save these resultsetsets
>to the Access DB.
>
>Thanks for any suggestions

[Simon Rawlings, MCSD]

One of the best ways to do this summary detail stuff is to use
ADO shaped recordsets. These shaped structures give a hierarchical structure
to the recordsource, and can also be linked to
a MSHFlexgrid control.

There is plenty of examples in MSDN on this stuff. Try
Q196029 for a simple example populating a MSHflexgrid.

Another article gives an overall description of what you can do gives you
different classifications of ways of using this. Out of the three types,
the one you probably want is (Cut from http://msdn.microsoft.com/library/te...n_newado20.htm)

Parameterized command hierarchies. A parameter hierarchy is similar to a
relation hierarchy. The main difference is that instead of downloading all
of the records for both the parent and child recordsets, the parents are
downloaded, and then the child records are downloaded as they are accessed
by the consumer application. This has the benefit of a shorter initial query
time, but the application must be connected to the server for new child records
to be accessed.

Don't be too put of by the syntax, it looks harder than it is.

SR

"Kamran Mahbub" <kmahbub@att.net> wrote:
>
>Of course that is not possible but you can programmatically create an ADO
>recordset (or load the main recordset w/o any records if you need it to
be
>empty) or CLONE the main recordset as many times as you need. Then you can
>use these copies of the rs for calculations or to save them locally. And
>it does not matter even if you change the data in these clones (for temporary
>use) as far as they are disconnected.
>
>Kamran
>
>
>"Faina" <Flekakh@kraft.com> wrote:
>>
>>Does anybody know if I can open a resultset and have a resultset as a data
>>source? For example:
>>
>>I open first resultset as
>> rs.CursorType = adOpenKeyset
>> rs.LockType = adLockOptimistic
>> rs.CursorLocation = adUseClient
>> rs.Open "SELECT * from psivert;"
>>
>>Then I would like to create a second resultset against rs that was just
>created.If
>>this is possible can I see the code sample
>>please?
>>
>>The reason I need to open a second recordset against first recordset is
>described
>>below. If there are any other ideas on how to handle it please let me know.
>>
>>I have to write an application that needs to recalculate spread
>>control on the screen at the same speed Excel does it(immediately), save
>>changes to the local Access 97 database and
>>refresh a second screen(2 screen has to be open at the same time)
>>to reflect changes from the first screen. First screen is a
>>detail level screen and 2nd screen is a summary screen.
>>If I can do all changes against resultsets in memory instead of DB it should
>>be much faster(I think). Also in order to
>>do what I need to do I should be able to run different SQL functions against
>>resultset in memory(sum, group by etc). Then
>>at predetermined time intervals I will save these resultsetsets
>>to the Access DB.
>>
>>Thanks for any suggestions
>