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
DatabasesMicrosoft AccessVisual Basic ClassicVBASQL

Avatar of undefined
Last Comment
Massimo Scola
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.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

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
Massimo Scola
Flag of Switzerland image

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

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
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/
Avatar of Massimo Scola
Massimo Scola
Flag of Switzerland image

ASKER

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.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo