Recordset Question

[gblack]

Let's say I have a recordset that looks something like:

rsItem.open("Select ItemID from tblItem")


Once I have established this recorset, is there a way I can make another recordset using the above recorset, but as a subquery?

Something like rsMain.open("SELECT * FROM tblMAIN where ItemID IN (" & rsItem & ")"

???

Thanks,
Gary

 

[pr2-eugin]

Gary, are you using ADODB recordset ? Or DAO? I guess ADODB, as DAO does not have a method as .Open.. Anyway, you can use something like..

rsMain.open("SELECT * FROM tblMAIN where ItemID IN (Select ItemID from tblItem)"

 

[gblack]

Yes, thanks... I appreciate your help, but I know what the SQL code looks like, for a subqeury. Although it might have read like that, that's not really what I was looking for. Writing this stuff down is a difficult thing sometimes.

What I am asking is: can "one" use a recorset (RS) that has already been defined, set, and opened, within another SQL statement in order to create a new and different recordset? In this case I would like to use my current recordset to get the output of my next RS.

My example was simplified, so as not to cause any confusion, but I guess that won't do the trick so... here goes: Basically I have a recurisve table and I want to use my prior recordset against the same table, looping over and over till I get to the end of all data stream outputs, from the recursive table...

Since I have already written a recordset for the the first level/Root of data, I am wondering... Can I use that same RS, within the next SQL statement, to write my next RS?

If I can, I can then put this lgic within a loop until I loop through all recursive data.

I am sure I can write the SQL in seperate parts and loop through that, but using the prior recordset data would be a lot easier imho... at least that's what I am thinking...


Also: DAO... ADO... I don't care... I could use either one if one allows for this and the other doesn't. Currently I am using DAO...

Sorry if I can't explain myself correctly... I rarely seem to be able to state exactly what I am looking for and I have been on this forum for years... Even with a poor question... you guys end often end up helping me figuring it out... which I find amazing...

So, much appreciated...
-Gary

 

[spikepl]

People do recursive searches when a hierarchy is stored using Adjacency List (where you in a record also store the ID of its hierarchical parent record). Search for recursive functions Access hierarchy.

If your hierarchy has a limited number of levels, then you can get away with some other tricks - many Access TreeView examples show how to do that, without recursion. Google.

Depending on what you want to do you can store your hierarchy in a different way: http://en.wikipedia.org/wiki/Nested_set_model . The setup is not quite intuitive, but allows for easy manipulation of entire branches oif the hierarchy.