Recursion on recordsets

[paoconnell]

I need to print a report of the structure of a multiway tree of nodes, stored as two tables in an Access 2000 database. The master table contains node information. The other table stores connections between nodes that have inputs and their inputs.

Fortunately, a simple way to print the structure is as an outline, with the top record at the left, and its input records printed below it. Judging from a quick browse of this group, recursion is in general OK in Access VBA

The simplest way to do this is a recursive function, called the first time with a record and an indent level, using FileSystemObject to print each line of the outline, with recursive calls to the function for each of the input nodes associated with the input node, at an incremented indent level. A record with no inputs simply prints and exits without additional calls to the function.

Unfortunately, this strategy means that each call to a node with (say) 4 inputs will generate four recursive calls to the function, each of which will have a local recordset. I'm wondering about stack overflow, here...

The alternative is to iterate carefully through the tree, using a stack object to simulate recursion. I've done this before on a tree in another project, but it's messy.

Has anyone out there done something similar? Is recursion in Access 2000 "safe" to use, or is the report likely to run out of stack space (the tree is wide, but only 4 deep at this point).

Thanks!

Pat O'Connell

 

[MichaelRed]

The stack depth for a recursive is <= the number of elements or recursions (i.e. the number of items which are traversed, so you can reasonabley estimate the number of items which may end up there. Wheather htis will fit into memory is a quite different question with numerous variables, including the specific O.S, the ammount of memory installed, what is loaded (startup programs etc.) and the size of your app.

Quick answer is that it is not at all possible to say from the info you have provided.

Intermediate answer is that even with ALL of the above noted info and more, it would be difficult and problematic.

Real answer is to suggest just trying it. Done carefully (and saving the app before attempting to run it -EACH TIME), the worst that should happen is that it will crash, and this probably will happen at least a few times in the development anyway. To test the routine, extract a small bit of the overall structure which you are sure will not generate a 'real' stack problem.

Also, look into methods to increase the stack space available to your routine.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[KenReay]

Hi

see

http://www.mvps.org/access/modules/mdl0027.htm

it may help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[paoconnell]

For those that replied, the recursive function worked just fine, first time (much to my surprise). The tree depth of four (with references to as many as four recordsets on the call stack) was not enough to overflow the stack. Deeper trees constructed by the users since initial tries have not been a problem either. Much better than using a stack to iterate through the tree (yes, I've done that also).

Pat O'Connell