Results 1 to 2 of 2

Recursive Function using inline query vs Stored Query

  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56

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


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


    End Function

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,715
    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
  •  
Tech Forums: Microsoft Office Forums