Results 1 to 12 of 12
  1. #1
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74

    Recursion and recordsets (too many databases open)


    I have a recursive function that references itself. It opens a recordset, updates the info and then reruns the subroutine until there are no more entries. It works perfectly for smaller data sets. The problem is, once I get 22 recordsets deep, it breaks due to too many databases open (I don't know the exact limit but it's probably about 25 is the max number of recordsets you can open at one time). So I need a workaround for this. My ideas so far are to either try to divide it into chunks (which is difficult because there are so many interdependent records) or I can try to use a multidimensional array to store all of the values and then write them to the recordset all in one shot at the end. Does anyone have any other ideas on this? I would love to be able to use the existing subroutine because it works so well, but I don't think there's a way around that limit. Open to any and all suggestions.

    In this code, DID stands for DataID

    Code:
    Public Sub RecalcDID (DID As Long, i As Long)
    Dim db As DAO.Database
    Dim ds As DAO.Recordset
    
    
        Set db = CurrentDb
        Set ds = db.OpenRecordset("Select * from DependencyQ where ThisDID = " & DID)
        
        Do Until ds.EOF
            ds.Edit
            ds!nextdv = Eval(ds!nexte)
            ds!nextir = True
            ds!nextcs = i
            ds.Update
            i = i + 1
            RecalcDID ds!NextDID, i
            ds.MoveNext
        Loop
        
        ds.Close
        db.Close
        
        Set ds = Nothing
        Set db = Nothing
        
    End Sub

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think the question to ask is how many deep your regression will go, before trying to give you the correct answer?
    You can up the limit on connections/databases but then machine memory and resources will probably become an issue.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Probably about 60 - 80 iterations in the worst cases

    Quote Originally Posted by Minty View Post
    I think the question to ask is how many deep your regression will go, before trying to give you the correct answer?
    You can up the limit on connections/databases but then machine memory and resources will probably become an issue.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    to be clear - do you know by inspection that you have 22+ levels? seems a lot to me. You are not in a loop?

    not sure if it will help, you can try moving

    Dim db As DAO.Database

    outside the function (together with the close/nothing lines) and set it once in the function that sets the seed for your recursive function.

    Also, don't know how many fields in your recordset, but just bring through the fields required - reduce the 'width'

    And the other thing to try is to use an update query rather than your current method - might make a difference

  5. #5
    msbad1959 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    2

    try this

    Code:
    Public Sub RecalcDID (DID As Long, i As Long)
        Dim ds As DAO.Recordset
        If Not rs Is Nothing Then
            rs.Close
            Set rs = Nothing
            Dim ds As DAO.Recordset
        End If
        Set ds = currentdb.OpenRecordset("Select * from DependencyQ where ThisDID = " & DID)
        
        Do Until ds.EOF
            ds.Edit
            ds!nextdv = Eval(ds!nexte)
            ds!nextir = True
            ds!nextcs = i
            ds.Update
            i = i + 1
            RecalcDID ds!NextDID, i
            'ds.MoveNext  this is never hit
        Loop
        
        ds.Close
        
        Set ds = Nothing
        
    End Sub

  6. #6
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    A previous version of the code had a counter that tracked how many recordsets were open in a temp variable, that's how I knew it's failing at 22 recordsets. An update query was my first attempt, but it took four hours to run, whereas this takes about 30-90 seconds.

    Code:
    Public Sub RecalcDID(DID As Long, i As Long)
    Dim db As DAO.Database
    Dim ds As DAO.Recordset
    
    
        Set db = CurrentDb
        Set ds = db.OpenRecordset("Select * from DependencyQ where ThisDID = " & DID)
        TempVars!tv_currentiteration = TempVars!tv_currentiteration + 1
        If TempVars!tv_currentiteration >= 100 Then Stop
        
        Do Until ds.EOF
            ds.Edit
            ds!nextdv = Eval(ds!nexte)
            ds!nextir = True
            ds!nextcs = i
            ds.Update
            Debug.Print ds!NextDID & " calculated. #" & TempVars!tv_currentiteration
            i = i + 1
            'If i Mod 15 = 0 Then Stop
            RecalcDID ds!NextDID, i
            ds.MoveNext
        Loop
        
        
        ds.Close
        db.Close
        TempVars!tv_currentiteration = TempVars!tv_currentiteration - 1
        
        Set ds = Nothing
        Set db = Nothing
        
    End Sub
    Quote Originally Posted by Ajax View Post
    to be clear - do you know by inspection that you have 22+ levels? seems a lot to me. You are not in a loop?

    not sure if it will help, you can try moving

    Dim db As DAO.Database

    outside the function (together with the close/nothing lines) and set it once in the function that sets the seed for your recursive function.

    Also, don't know how many fields in your recordset, but just bring through the fields required - reduce the 'width'

    And the other thing to try is to use an update query rather than your current method - might make a difference

  7. #7
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    This gives me an error for duplicate variable declared in same scope - what's it supposed to do? and should that say ds instead of rs?

    Quote Originally Posted by msbad1959 View Post
    Code:
    Public Sub RecalcDID (DID As Long, i As Long)
        Dim ds As DAO.Recordset
        If Not rs Is Nothing Then
            rs.Close
            Set rs = Nothing
            Dim ds As DAO.Recordset
        End If
        Set ds = currentdb.OpenRecordset("Select * from DependencyQ where ThisDID = " & DID)
        
        Do Until ds.EOF
            ds.Edit
            ds!nextdv = Eval(ds!nexte)
            ds!nextir = True
            ds!nextcs = i
            ds.Update
            i = i + 1
            RecalcDID ds!NextDID, i
            'ds.MoveNext  this is never hit
        Loop
        
        ds.Close
        
        Set ds = Nothing
        
    End Sub

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    had a counter that tracked how many recordsets were open in a temp variable, that's how I knew it's failing at 22 recordsets.
    that wasn't what I was asking. I was asking if you know from inspection of your dependancyQ table whether that level is realistic

    No idea what your data is, or what your function is supposed to achieve but in response to Micron you said 60-80 iterations. I'm just trying to imagine what that might be.

    I had a recursive function that mapped organisation ownership - around 300k records but only 10 levels deep for example

  9. #9
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    It's financial forecasts by month for the next 5 years, so when I say this month's beginning balance depends on last month's ending balance, I automatically know that I'm going to be going 60 iterations (12 months x 5 years)... right?

    Quote Originally Posted by Ajax View Post
    that wasn't what I was asking. I was asking if you know from inspection of your dependancyQ table whether that level is realistic

    No idea what your data is, or what your function is supposed to achieve but in response to Micron you said 60-80 iterations. I'm just trying to imagine what that might be.

    I had a recursive function that mapped organisation ownership - around 300k records but only 10 levels deep for example

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Thanks for the explanation.

    Done a fair amount of that over the years. Had to do a weekly forecast of P&L, BS and cashflow going forward two years and across some 50 subsidiaries plus consolidation.

    I think you will struggle using a recursive function. Depends how your data is organised, but suggest use an aggregate query utilising a non standard join (i.e. one that you can't build in the query builder) to a table which identifies your year/months. No point going into detail without knowing your data structure but simplistically


    Code:
    SELECT yrMth, sum(OB.tranamount) as OpeningBal, sum (CB.tranamount) as ClosingBal
    FROM (tblYearMonth YM LEFT JOIN tblTrans OB ON OB.yrMth<YM.yrMth) LEFT JOIN tblTrans CB ON CB.yrMth<=YM.yrMth
    GROUP By yrMth
    ORDER By yrMth

  11. #11
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Thanks Ajax. However, the issue isn't viewing the data, it's a matter of evaluating interdependent formulas in the correct order that are different for every line item. I'm basically trying to reverse engineer the excel recalculation algorithm and stick it in access so each month/line item combo can have a unique formula. Why, you wonder? I think mostly because my boss wants to see if I can, but also because we have over 100 clients we need to forecast for and separate spreadsheets for each client isn't very efficient. We also want to be able to easily compare clients and have standard reporting output, among other things. I had a recalculation algorithm that worked great, except for one line item (but it was a very important line item). So, long story short, if you have a lot of experience with access and financial forecasting, do you do any consulting work and can I hire you? I've been given a budget to get outside help and I have had a very difficult time finding someone who knows access, VBA and financial forecasting as well as I do.

    Quote Originally Posted by Ajax View Post
    Thanks for the explanation.

    Done a fair amount of that over the years. Had to do a weekly forecast of P&L, BS and cashflow going forward two years and across some 50 subsidiaries plus consolidation.

    I think you will struggle using a recursive function. Depends how your data is organised, but suggest use an aggregate query utilising a non standard join (i.e. one that you can't build in the query builder) to a table which identifies your year/months. No point going into detail without knowing your data structure but simplistically


    Code:
    SELECT yrMth, sum(OB.tranamount) as OpeningBal, sum (CB.tranamount) as ClosingBal
    FROM (tblYearMonth YM LEFT JOIN tblTrans OB ON OB.yrMth<YM.yrMth) LEFT JOIN tblTrans CB ON CB.yrMth<=YM.yrMth
    GROUP By yrMth
    ORDER By yrMth

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So, long story short, if you have a lot of experience with access and financial forecasting, do you do any consulting work and can I hire you?
    I do do consulting work, that is my business. I'll PM you a message

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

Similar Threads

  1. Open Multiple Databases
    By neuroman in forum Modules
    Replies: 1
    Last Post: 03-06-2016, 10:25 PM
  2. DAO.Recordsets - When to close and open
    By jgelpi16 in forum Programming
    Replies: 11
    Last Post: 02-08-2011, 09:16 AM
  3. Cannot open any more databases.
    By evander in forum Queries
    Replies: 7
    Last Post: 08-24-2010, 12:22 AM
  4. Cannot open any more databases error
    By Matthieu in forum Access
    Replies: 2
    Last Post: 04-14-2010, 03:29 PM
  5. Replies: 1
    Last Post: 04-11-2010, 04:05 AM

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