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