Is it possible to create a recursive query in Access?

by

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).

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. How to get random record from MS Access database
  5. create form to add records in multiple tables
  6. Operation must use an updatable query. (Error 3073) Microsoft Access
  7. How do I force VBA/Access to require variables to be defined?
  8. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  9. Running Microsoft Access as a Scheduled Task
  10. How to diagnose MS access crashes
  11. “Operation must use an updateable query” error in MS Access
  12. How to restart counting from 1 after erasing table in MS Access?
  13. Equivalent cURL in VBA?
  14. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  15. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  16. MS Access trigger?
  17. Insert SQL command with Datetime in MS-Access
  18. Is MS Access (JET) suitable for multiuser access?
  19. VBA procedure to import csv file into access
  20. How to select top 10 in Access query?
  21. ConcatRelated() in Access, using two keys
  22. Concatenating multiple rows into single line in MS Access [duplicate]
  23. Transform multiple rows into columns with unique key
  24. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  25. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  26. Custom row source for combo box in continuous form in Access
  27. VBA – Run Time Error 3271 using DAO object
  28. Working with multiple programmers on MS Access
  29. Trouble with InputBoxes
  30. Get Name of Current VBA Function

Leave a Comment