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.

Answer 1

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

Answer 2

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.