Is It possible to create recursive query in access?

Question

I have a table

Id
ParentID
jobName
jobStatus

The root ParentID is 0.

Is it possible is Access to create a query to find a root for a given job. Database is mdb no linked tables. Access version is 2003. job can be in several grand child level.

Any help would be appreciated.

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 levers beforehand, you could write a qry, 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.

Answer 2

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.

Answer 3

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