Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

asked on

Access 2016: Using recursions to crawl through table with self-join

I am developing an employee database. I added all employees to an organisational unit (department etc) - and each organisational unit belongs to another organisational unit, except for the top one which is blank; the CEO. With a self-join, I can then create a hierarchy once all the data has been added.

 
 User generated image

I tried creating a function which would populate a collection from the uppermost organisational unit (the CEO) all the way down and I used recursions to traverse the tree.

Function preorderProcessing(parentID As Integer)

    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim x               As Integer
    
    Set MyDB = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set colOrgUnits = New Collection
    
    If parentID = 0 Then
        Set qdf = MyDB.QueryDefs("qyrOrgUnits_SearchParentID_Null")
    Else
        Set qdf = MyDB.QueryDefs("qryOrgUnits_SearchParentID")
        qdf.Parameters("parentID") = parentID
    End If
    
    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset
    
    Do While Not rs.EOF
      colOrgUnits.Add (rs.Fields("org_OrganisationUnitID"))
      rs.MoveNext
    Loop
    rs.Close
    
    'debug
    Debug.Print "Now checking units: "
    For x = 1 To colOrgUnits.Count
        Debug.Print "In this stack: " & colOrgUnits.Item(x)
    Next x
    
    Debug.Print "COUNT: " & colOrgUnits.Count
    If colOrgUnits.Count > 0 Then
        For x = 1 To colOrgUnits.Count
            Debug.Print "Organisational Unit: " & colOrgUnits.Item(x)
            preorderProcessing (colOrgUnits.Item(x))
        Next x
    End If
    
End Function

Open in new window


The problem with this function is, that I get a run-time error 5: Invalid procedure call or argument.
This happens when the recursion reaches the organisational unit #10 - the assistant.

What do I need to do, in order to make this code work .. and that it will add all organisational units from the topmost downwards into the collection?
Or is this an issue with recursions?

Thanks for your help

Massimo
DB.accdb
Avatar of aikimark
aikimark
Flag of United States of America image

Which line is producing the error?
What is/are the expression value(s) on that line when it fails?

Do you know how deep you are in the recursion?

My gut recommendation is to break this up where you don't create a copy of the database object every time or replace the currentdb with dbengine(0)(0).

Unless the query is overly complicated, drop the querydef and use a simpler SQL string.
I guess it is because you for every iteration reset the collection, which may exit the calling loop:

    Set colOrgUnits = New Collection

Open in new window


If you move the collection inside the function:

    Dim colOrgUnits     As Collection
    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim x               As Integer

    Set colOrgUnits = New Collection
    Set MyDB = CurrentDb
    Dim qdf As DAO.QueryDef

Open in new window

it seems to run:

preorderProcessing 0
Now checking units: 
In this stack: 4
COUNT: 1
Organisational Unit: 4
Now checking units: 
In this stack: 8
COUNT: 1
Organisational Unit: 8
Now checking units: 
In this stack: 10
In this stack: 12
In this stack: 13
In this stack: 17
In this stack: 22
In this stack: 25
COUNT: 6
Organisational Unit: 10
Now checking units: 
COUNT: 0
Organisational Unit: 12
Now checking units: 
COUNT: 0
Organisational Unit: 13
Now checking units: 
In this stack: 14
In this stack: 15
In this stack: 16
COUNT: 3
Organisational Unit: 14
Now checking units: 
COUNT: 0
Organisational Unit: 15
Now checking units: 
COUNT: 0
Organisational Unit: 16
Now checking units: 
COUNT: 0
Organisational Unit: 17
Now checking units: 
In this stack: 18
In this stack: 19
In this stack: 20
In this stack: 21
COUNT: 4
Organisational Unit: 18
Now checking units: 
COUNT: 0
Organisational Unit: 19
Now checking units: 
COUNT: 0
Organisational Unit: 20
Now checking units: 
COUNT: 0
Organisational Unit: 21
Now checking units: 
COUNT: 0
Organisational Unit: 22
Now checking units: 
In this stack: 23
In this stack: 24
COUNT: 2
Organisational Unit: 23
Now checking units: 
COUNT: 0
Organisational Unit: 24
Now checking units: 
COUNT: 0
Organisational Unit: 25
Now checking units: 
In this stack: 26
In this stack: 27
In this stack: 28
In this stack: 29
COUNT: 4
Organisational Unit: 26
Now checking units: 
COUNT: 0
Organisational Unit: 27
Now checking units: 
COUNT: 0
Organisational Unit: 28
Now checking units: 
COUNT: 0
Organisational Unit: 29
Now checking units: 
COUNT: 0

Open in new window

Avatar of Massimo Scola

ASKER

Yes that was the issue!
However, I noticed something strange..  

I've added the values into a new table and I do seem to get a hierarchy - but it is the wrong way round.
The CEO is at the bottom - instead of at the top

    
    For x = 1 To colOrgUnits.Count
        MyDB.Execute "INSERT INTO tblOrganisation (OrganisationID) VALUES (" & colOrgUnits.Item(x) & ");"
    Next x

Open in new window


I tried to reverse the way it is added to the table

    For x = colOrgUnits.Count To 1 Step -1
        MyDB.Execute "INSERT INTO tblOrganisation (OrganisationID) VALUES (" & colOrgUnits.Item(x) & ");"
    Next x

Open in new window


But I still have the same problem.

User generated image
Is it the way I am adding the items/the organisational units into the collection?

I've attached the DB with the changes.
DB2.accdb
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Maybe you'll find the nested set model usefull.
A query will do all the "recursive" work.

See this article:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
Now it works - I will try it at work tomorrow with the original DB.
Thanks a lot for the explanation Gustav. Also thanks Fabrice for the link. I will read it when I have a bit more time tomorrow.