Link to home
Start Free TrialLog in
Avatar of jamiei
jamiei

asked on

Cannot open any more tables

So, I've had some help with getting this function to populate a treeview with an ownership structure of a company.  It works good for most companies, however when I get to a company that owns say, 100 companies and all of them have their own ownership structures too, I get an error 'Cannot open any more tables' and when debugged it goes to the rst.open statement in the function.  I have read that 1022 is the maximum number of recordsets that can be opened, and it's possible and probable that that is being reached and exceeded thus causing the problem.

The question I have is if anyone knows a different way for me to populate a treeview with a companies ownership structure when a company is selected.  I will paste the code I am currently using and any help would be appreciated.

***This is where I populate the parent node with the number of companies owned
Sub ShowOwnsCount()
Dim rnode As node
Dim strSelect As String
Dim company As String

    cn.Execute "Delete * from [#tblNodeTemp] where left([OwnID],4) = '" & Left(EntID, 4) & "'"
    TreeView2.Nodes.Clear

    If Right(EntID, 4) = "0000" Then
        WhereEntID = "Left([Owners].[OwnID],4) = '" & Left(EntID, 4) & "'"
    Else
        WhereEntID = "[Owners].[OwnID] = '" & EntID & "' or [Owners].[OwnID] = '" & Left(EntID, 4) & "0000'"
    End If

    Set rst = New ADODB.Recordset

    sqlString = "Insert Into [#tblNodeTemp] ([EntID], [LegalName], [OwnID]) Select DISTINCT [Owners].[EntID] As       [EntID], [Entities].[LegalName] As [LegalName], left([Owners].[OwnID],4) As [OwnID] FROM [Owners] INNER JOIN [Entities] ON [Owners].[EntID] = [Entities].[EntID] where " & WhereEntID & _
                "ORDER BY [Entities].[LegalName], [Owners].[EntID]"

    cn.Execute sqlString

    strSelect = "SELECT DISTINCT 'Owns' AS Expr3, Count([#tblNodeTemp].[LegalName]) AS CountofOwns From [#tblNodeTemp] GROUP BY 'Owns', Left([OwnID],4) having left([ownid],4) = '" & Left(EntID, 4) & "'"

    rst.Open strSelect, cn

    Do While Not rst.EOF
        Set rnode = TreeView2.Nodes.Add(, , , rst!Expr3 & " (" & rst!countofowns & ")")
        TreeView1.Height = 1532
        TreeView2.Height = 5052
        TreeView2.Top = 3560
        company = EntID
        MousePointer = vbHourglass
        *****This is the function that I call that is recursive and is where the error occurs *****
        AddCompanies company, rnode
        MousePointer = vbDefault
       ' If rst!countofowns > 0 Then 'children so add the dummy
       '     Set rnode = TreeView2.Nodes.Add(rnode, tvwChild, , "Dummy")
       ' End If
        rst.MoveNext
    Loop
    rst.Close

    TreeView2.Top = 3560
End Sub

***Function causing error *****
Private Sub AddCompanies(parentCompany As String, parentNode As node)
Dim rst As ADODB.Recordset
Dim node As node
Dim company As String
Dim strSQL As String

Set rst = New ADODB.Recordset
strSQL = "Select Distinct Owners.EntID, Entities.LegalName FROM ([Entities] INNER JOIN Owners ON [Entities].[entid] = Owners.EntID) where left([ownid],4) = '" & Left([parentCompany], 4) & "'"
****where the debug takes when error comes up*****
rst.Open strSQL, cn

While Not rst.EOF
    company = rst!EntID & " - " & rst!LegalName
    Set node = TreeView2.Nodes.Add(parentNode, tvwChild, , company)
    AddCompanies Left(company, 8), node
    rst.MoveNext
Wend
rst.Close
Set rst = Nothing


End Sub

Thank yoU!
Avatar of Preece
Preece
Flag of United States of America image

In AddCompany, print to the immediate (debug) window the contents of strSQL just before each call to open the recordset:

debug.print  strSQL
rst.Open strSQL, cn

Then have a look at the sql string each time the call is made.  You can copy and paste the sql string in question into an Access query or the query analyzer of sql server.  

Hmmmmm, after looking closer at the question, it looks like the recursive calls in AddCompany may not be necessary.  

Try removing the line:

AddCompanies Left(company, 8), node


Hope this helps!
Preece
Also, I think that after this line:
Set node = TreeView2.Nodes.Add(parentNode, tvwChild, , company)

you need to destroy the temp node:
Set node = Nothing
SOLUTION
Avatar of zvonkodj
zvonkodj

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jamiei
jamiei

ASKER

This causes an error every time, the rst.absolutepositiion is -1 in value and it won't accept this.  I don't understand how this will solve the problem anyway, can you explain as well as let me know what is wrong?
You are using a recursive function and you can not predict how many times will this function be called. If your recursive function should be 1500 times called, you want to open 1500 different recordSets. It is wrong in your solution.

This the only way to solve your problem is to populate your TreeView with only one RecordSet. Insted of having 1500 or more RecordSets, you have to open, close and reopen this one RecordSet 1500 or more times. But, in this case, after reopening of RS, you have to set the old (previous) position of cursor of this RS

Please try this:

1. After every rst.open you have to add two Commands: rst.movelast and rst.movefirst
2. You should define RS_AbsPos as Variant, not as long
3. Instead of RS_AbsPos = rst.AbsolutePosition  you sholuld write RS_AbsPos = rst.Bookmark
4. Instead if  rst.AbsolutePosition = RS_AbsPos  you should write  rst.Bookmark = RS_AbsPos

Very important: delete definition of RS in your recursive function and define your RS somewhere else.

I don't know much about items 2, 3, and 4, but I completely agree with zvonkodj on the rest that post.
Avatar of jamiei

ASKER

This give me an error of 'not a valid bookmark' and takes me to the rst.bookmark = rs_abspos.  Unfortunately I don't think this solution is going to work for me.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial