Is it possible to create a recursive query in Access?

Question

I have a job table

Id
ParentID
jobName
jobStatus

The root ParentID is 0.

Is it possible in Access to create a query to find a root for a given job? The database is MDB with no linked tables. The Access version is 2003. A job can be several levels grand children deep.

Bois

Please be more precise when you say "job can be in several grand child level" or paste some dummy data. – Every

Answer 1

No, It isn't. Recursive queries are supported in SQL Server after SServer 2005, but not in Access.

If you know the number of levels beforehand, you could write a query, but it wouldn't be a recursive one.

In SQL Server, CTE (An SQL extension) is used for that : see http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx

Regular SQL however does not have Recursivity support.

Grummet

@onedaywhen: Does/should "ISO/ANSI Standard SQL" have a suffix year, to help distinguish it from "ISO/ANSI Standard SQL-99"? – Internist

Answer 2

It is possible in Access to create a query to find the root of your given job. Don't forget the power of VBA functions. You can create a recursive function in a VBA module and use its result as an output field in your query.

Example:

Public Function JobRoot(Id As Long, ParentId As Long) As Long
   If ParentId = 0 Then
      JobRoot = Id
      Exit Function
   End If

   Dim Rst As New ADODB.Recordset
   Dim sql As String
   sql = "SELECT Id, ParentID FROM JobTable WHERE Id = " & ParentId & ";"
   Rst.Open sql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

   If Rst.Fields("ParentID") = 0 Then
      JobRoot = Rst.Fields("Id")
   Else
      JobRoot = JobRoot(Id, Rst.Fields("ParentID"))    ' Recursive.
   End If

   Rst.Close
   Set Rst = Nothing
End Function

You can call this recursive function from your query by using the query builder or by just typing in the function name with arguments in a query field.

It will yield the root.

(I recognize the OP is a year old now, but I'm compelled to answer when everyone says what's impossible is possible).

Unready

While I applaud your posting, the original question actually does restrict the answer to a query, which is, in fact, impossible without VBA. Whether or not it's fair to interpret "in a query" as meaning "without dependency on custom VBA functions" is another issue. I think it's good to have your solution here even if it's not what the original poster was looking for because other's with the same problem may have no qualms about it not being a pure SQL answer. – Parboil

This is extremely inefficient, as it means opening a new recordset each time it is called. I would suggest at minimum keeping the recordset in a module-level variable, or to load the entire recordset into an in-memory module-level Scripting.Dictionary which would probably allow faster access, like this. @Parboil – Salicylate

Answer 3

You can't recursively query.

You can either do some arbitrary number of left joins, but you'll only be able to go up as many levels as you have joins.

Or you can use Celko's "Nested Set Model" to retrieve all parents. This will require modifying your table structure, in way that makes inserts and updates more complicated.

Egis

You can't recursively query. -> Not in access 03 anyways, in many other query langs you can – Grummet

Yes, "you" the OP, who is using Access, can't recursively query. – Egis

Although nested sets were popularized by Celko, the method is credited to Michael J. Kamfonas (en.wikipedia.org/wiki/Joe_Celko). – Wiretap

Answer 4

This cannot be done using pure SQL in Access, but a little VBA goes a long way.

Add a reference to the Microsoft Scripting Runtime (Tools -> References...).

This assumes that the ID is unique, and that there are no cycles: e.g. A's parent is B, but B's parent is A.

Dim dict As Scripting.Dictionary

Function JobRoot(ID As Long) As Long
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("SELECT ID, ParentID FROM Job", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict(rs!ID) = rs!ParentID
            rs.MoveNext
        Loop
        Set rs = Nothing

        Dim key As Variant
        For Each key In dict.Keys
            Dim possibleRoot As Integer
            possibleRoot = dict(key)
            Do While dict(possibleRoot) <> 0
                possibleRoot = dict(possibleRoot)
            Loop
            dict(key) = possibleRoot
        Next
    End If
    JobRoot = dict(ID)
End Function

Sub Reset() 'This needs to be called to refresh the data
    Set dict = Nothing
End Sub

Salicylate

Answer 5

OK so here's the REAL deal. First, what is the target audience for your query.. a form? report? function/proc?

Form: Need updates? use the treeview control while clumsy it will work nicely. Report: in the open event use a parameter form to set the "Boss Job" level then handle the recursion in vba and fill a recordset with the data in the order desired. set the reports recordset to this filled recordset and process the report. Function/Procedure: works pretty much the same as a the data load described in the report above. Through code, handle the necessary "tree walking" and store the result set in the desired order in a recordset and process as needed.

Minstrelsy

What Treeview control? There is no native Treeview available in any version of Access of which I'm aware. – Parboil

This could be a good addition to the existing answers, but it needs work. For example, Delete "OK so here's the REAL deal."; Break the options into bullets or paras. Expand & clarify each one. – Internist

@David-W-Fenton, maybe it wasn't when you posted, but is now. I use Access 2010 and has TreeView included as an ActiveX control. – Cur

Answer 6

Zev's contribution gave me a great deal of inspiration and learning. However, needed to do some edits to the code. Please note that my table is called "tblTree".

Dim dict As Scripting.Dictionary

Function TreeRoot(ID As Long) As Long
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary ' Requires Microsoft Scripting Runtime
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("tblTree", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict.Add (rs!ID), (rs!ParentID)
            rs.MoveNext
        Loop
        Set rs = Nothing
    End If

    TreeRoot = ID

    Do While dict(TreeRoot) <> 0    ' Note: short version for dict.item(TreeRoot)
        TreeRoot = dict(TreeRoot)
    Loop
End Function

And there is another useful function in the same context. "ChildHasParent" returns true, if the child's matches the supplied ParentID in any level of nesting.

Function ChildHasParent(ID As Long, ParentID As Long) As Boolean
    If dict Is Nothing Then
        Set dict = New Scripting.Dictionary ' Requires Microsoft Scripting Runtime
        Dim rs As DAO.Recordset
        Set rs = CurrentDb.OpenRecordset("tblTree", dbOpenForwardOnly, dbReadOnly)
        Do Until rs.EOF
            dict.Add (rs!ID), (rs!ParentID)
            rs.MoveNext
        Loop
        Set rs = Nothing
    End If

    ChildHasParent = False

    Do While dict(ID) <> 0    ' Note: short version for dict.item(TreeRoot)
        ID = dict(ID)
        If ID = ParentID Then
            ChildHasParent = True
            Exit Do
        End If
    Loop
End Function

Telemachus

Answer 7

I had a related problem working with a treeview structure, when a user wants to delete a node, he wants to delete all then nodes under that tree. Childs, childs of childs etc etc.

A job for recursion....

So to delete the data in your table to match the treeview node deletes, use a recursive function that will delete the node, and recurse down and delete all the nodes that are children, grandchildren ect.

example of the function:

Public Sub RemoveChildKeys(MyKey)
' deletes passed key and removes all children and grandchildren ect etc of passed key recursively

   Dim TheDB As DAO.Database
   Dim TheTable As DAO.Recordset
   Dim MySql As String

   Set TheDB = CurrentDb

   MySql = "Select * from TblIndex WHERE [Parent]=" & MyKey & ";"
   Set TheTable = TheDB.OpenRecordset(MySql)

   While Not TheTable.EOF
     RemoveChildKeys (TheTable!Key)  ' <---- Calls itself
     TheTable.MoveNext
   Wend

   DoCmd.RunSQL "Delete * FROM TblIndex WHERE [Key]=" & MyKey  ' delete in table

End Sub

Excited