Recursive limit in Access?

  • Thread starter Thread starter Ray Cacciatore
  • Start date Start date
R

Ray Cacciatore

I'm trying to implement a recursive function in Access. I
have a table that references itself. Two columns are
used, one is the parent id, the other is the child id.

The problem is that my recursive function calls itself so
many times that I get the error message:"Cannot open any
more tables". I have a line that opens a recordset in my
function using the CurrentDb.OpenRecordset method. It
looks like this line opens too many databases and
eventually shows the error message.

Here's the code for your convenience. Please Help.

Function LoadCategory(sId) As Boolean
Dim rs As DAO.Recordset
Dim sql As String
Dim db As DAO.Database

'Check for the bottom of the tree
If IsNull(sId) Then
Exit Function
End If
Set db = CurrentDb

'/// put all the children of sID into a recordset
sql = "select * from tblMyTable WHERE mYear=2003 and
ParentCCID='" & sId & "'"
Set rs = CurrentDb().OpenRecordset(sql)

Do While Not rs.EOF
'Only print if it's a leaf node
If Len(rs("CCID")) = 5 Then
Debug.Print rs("CCID")
End If
'Recursive call to check for children
Call LoadCategory(rs("CCID"))
rs.MoveNext
Loop

If Not rs Is Nothing Then
rs.Close
End If
Set rs = Nothing

End Function

Is there another way to tackle the problem without having
to open too many recordsets.

Thanks in advance

Ray C.
 
Ray:

It doesn't appear that you posted all the code in your current solution.
Based on what you've got currently, there's only one open db and one open
recordset. My guess is that in Load Category you do more db setting and rs
opening etc.

The primary way to work with this is to:

1.) Only maintain one reference to a db object; pass it to sub procedures or
functions that also operate on the same db object. Rather than
instantiating and uninstantiating a new db object each time. If you can do
the same with the recordset itself thats an even better method.
2.) If all you are passing to LoadCategory is a value, then rather than
passing a recordset object (field), set the value of the field into a
variable and pass that variable to LoadCategory (or try passing
rs("CCID").value rather than the rs field itself). Each time you pass a
recordset object to the sub it may be persisting that object (i.e. failing
to clean up properly when the called sub finishes, leaving a counter
incremented).
3.) Possibly change to using DbEngine (0)(0) for your db reference rather
than CurrentDb. DbEngine (0)(0) is a bit faster and uses less overhead.
Its draw back is that its collection of objects (tables, queries) is not
refershed when new persistant objects are created in the db via the user
interface until the db is closed and re-opened. If DbEngine creates the
object itself, no problem.
4.) Make sure that you close out all recordsets that are ever opened when
you are done using them. (say in called sub procedures and functions.)
 
Ray,

Apart from any possible errors in your code (none jump up at me from my
brief reading), how about a data error?

If - for example - one of your records has a ParentCCID equal to its own
CCID, then you have built an infinite loop that will (eventually) violate
any Access limit, however generous. Similar problems could exist with
two-record or three-record circular references, etc.

Do you have anything in place to ensure that this can't occur?

CD
 
According to an earlier post by Sandra Daigle you reached the internal limit
on open tables. One option is to use a collection to store the CCID.


Here is a sample

'------- START CODE ---------
Sub TEST_Recurse()
On Error GoTo Proc_Error
Dim lngParentID As Long
Dim colParents As Collection
Dim oRS As DAO.Recordset
Dim strSQL As String
Dim I As Long
Dim lngMaxParents As Long
Dim bolContinue As Boolean

Set colParents = New Collection
colParents.Add lngParentID
I = 1
lngParentID = 129467
bolContinue = True

Do Until bolContinue = False
strSQL = "SELECT * FROM tblSysReportsBookMarks WHERE ParentID=" &
colParents.Item(I)
Set oRS = CurrentDb().OpenRecordset(strSQL)
Do While Not oRS.EOF
colParents.Add oRS.Fields("ChildID").Value
Debug.Print "PARENT: & " & colParents.Item(I) & " CHILD: " &
oRS.Fields("ChildID").Value
oRS.MoveNext
Loop
I = I + 1
lngMaxParents = colParents.Count
bolContinue = Not (I = lngMaxParents)
If Not oRS Is Nothing Then Set oRS = Nothing
Loop

Proc_Exit:
If Not oRS Is Nothing Then Set oRS = Nothing
If Not colParents Is Nothing Then Set colParents = Nothing
Exit Sub
Proc_Error:
MsgBox Err.Description
Resume Proc_Exit
End Sub
'-------- END CODE --------------


HTH

Jose
 
(snip)
If - for example - one of your records has a ParentCCID equal to its own
CCID, then you have built an infinite loop that will (eventually) violate
any Access limit, however generous.
Click to expand...

That was exactly the cause of the same error in one of my recursive (data
access) procedures.

To the OP, put in a debug statement (or whatever) to check you are not
executing the procedure(s) 50 gazillion times (due to an infinite data or
processing loop).

HTH,
TC
 
Thanks for the info.
It turned out that my code did not check for the
ParentCCID equalling its own CCID, causing an infinite
loop. Thanks !!!!!!

Ray
 
Ray said:
Thanks for the info.
It turned out that my code did not check for the
ParentCCID equalling its own CCID, causing an infinite
loop. Thanks !!!!!!
Click to expand...

Good call, Chadlon!
 
Back
Top