Results 1 to 2 of 2
  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Ottawa, ON

    Recursive Function using inline query vs Stored Query

    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
    FindNextParentKeyEquip = FindNextParentKeyEquip(rs!keyEquipUp)
    End If

    End If
    Set rs = Nothing
    Set Db = Nothing

    End Function

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Solving recursive query issue?
    By tonygg in forum Queries
    Replies: 4
    Last Post: 11-09-2017, 05:44 AM
  2. Replies: 0
    Last Post: 08-29-2017, 02:49 AM
  3. Recursive SQL Query in MS Access
    By skumar in forum Access
    Replies: 1
    Last Post: 08-24-2017, 07:32 AM
  4. Recursive query of companies ownerships
    By hunsnowboarder in forum Queries
    Replies: 13
    Last Post: 03-24-2016, 10:02 AM
  5. Is a recursive query possible?
    By nigelbloomy in forum Queries
    Replies: 3
    Last Post: 09-10-2015, 04:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums