Vba for Access Recursive Question

Hi Jim, this is a good example, my problem is to once I have the data into recordset or arrays, handle it inside the function with arrays and make it recursive, and have a result as an array that can be sent back into a table, if needed. Thanks. Carlos

Carlos,

 Access has no way directly of writing an array out to a record set or table (there is no corresponding function to GetRows()).   Given that, it’s best if you simply write the output right to a table  and I would skip using arrays entirely.    

  I can’t see where using arrays is going to gain you anything.  You’d be better off using a collection or a dictionary class rather than using arrays.

    But even with those,   I’m not sure it would be worth the overhead in setting them up, performing your operations, and then reading everything out to a table when done. Reading and writing from tables directly is pretty fast if things are properly indexed.  JET also manages its cache pretty well.

But in regards to your question, making a recursive call to a function is no different from the example even though it is using tables.   At the places where it works with tables, you would just work with your array.

Jim

I'll suggest the nested set model instead of the (classic) hierarchical model.
Queries might be more comple, but you'll have less of them, and far less VBA code (since queries can handle most of the job).

Take a look at this article:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Hi Jim,
kindly find attached the code I made before know your approach, I was able to getrows from tables using a recordset and then to an Array, my problem comes when I want to start looping for grandchild ensambles. Best regards. Carlos
Code.txt

Thanks Fabrice for your approach. Carlos

Can you give some more info on your issue...
Arrays can be easily passed as variant argument...something like that :

Private Sub SomeArrayHandling(AnArray As Variant)
Dim ArrayCounter As Integer
If Not isArrayEmpty(AnArray ) Then
For ArrayCounter = LBound(AnArray ) To UBound(AnArray )
'Do some work...if necessary recursion
Next ArrayCounter
End If
End Sub

Select allOpen in new window

and the helper function

Public Function isArrayEmpty(parArray As Variant) As Boolean
'Returns false if not an array or dynamic array that has not been initialised (ReDim) or has been erased (Erase)

    If IsArray(parArray) = False Then isArrayEmpty = True
    On Error Resume Next
    If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True: Exit Function Else: isArrayEmpty = False

End Function

Select allOpen in new window

Thanks John

Jim Dettman has helped me in stablish a simpler proceeding that will help me in the development the main solution, although it stablish the core algorithm necessary to solve my initial question it has not resolved the complete problem of the initial question, I think the help from Jim placed a big step toward the solution.

Honestly speaking I am new at EE, and I expected a full algorithm for my initial question. I guess I have to work harder, in develop my capabilities. But the help from the expert Jim was crucial.

Best regards
Carlos

Thanks for your extremely valuable help, please keep sharing your knowledge.