Recursive Function using inline query vs Stored Query

[greatwhite]

Below is a recursive query I am using.



I was wondering if it would be better if the inline query portion was called as a stored query instead. Would it save anytime. It isn't a very complicated query.



Function FindNextParentKeyEquip(ParamKeyEquipUp As Long) As String


Dim strSQL As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset


'Recursive function
'The function will continue to call itself until it finds a PWR-PL or PF-PL or it will return Null


strSQL = " SELECT "
strSQL = strSQL & " tblEquip.strFPN "
strSQL = strSQL & " , tblCable.keyEquipUp , tblCable.keyEquipDn "
strSQL = strSQL & " FROM tblEquip LEFT JOIN tblCable ON tblEquip.keyEquip = tblCable.keyEquipDN "
strSQL = strSQL & " WHERE tblEquip.keyEquip= " & ParamKeyEquipUp & " AND tblEquip.keyShip = " & txtHoldOptionValue


Set Db = CurrentDb

Set rs = Db.OpenRecordset(strSQL)


If IsNull(rs!keyEquipUp) Then
FindNextParentKeyEquip = "Null"
Exit Function
End If


If Not rs.EOF Then
'If InStr(rs!strFPN, "PWR-PL-") > 0 Then
If ((InStr(rs!strFPN, "PWR-PL-")) Or (InStr(rs!strFPN, "PF-PL-"))) > 0 Then
FindNextParentKeyEquip = rs!strFPN
Else
FindNextParentKeyEquip = FindNextParentKeyEquip(rs!keyEquipUp)
End If


End If
rs.Close
Set rs = Nothing
Db.Close
Set Db = Nothing


End Function

[ranman256]

I find that 99% of the work can be done from queries.
some things must be done via code.
i think yours could be a query.