Results 1 to 3 of 3

Thread: Split a branch into nodes, reconstrucing and running a query on each = inefficient?

  1. #1

    Thread Starter
    PowerPoster cafeenman's Avatar
    Join Date
    Mar 2002
    Location
    Florida
    Posts
    2,646

    Split a branch into nodes, reconstrucing and running a query on each = inefficient?

    Anyone have a better idea how to do this?

    Working demo attached. I got the whole tree/branch/node thing sorted and like it.

    Tree.zip

    Also, DO NOT click create tree until you look at the Editor or you will wipe out the tree in the db.

    Edit: This isn't in the upload but when I started this I was more interested in getting it working than anything else. So all the generic DB stuff in frmTree has been moved to the DB module.

    Code:
    Private Sub PopulateParentText()
    Dim RST As DAO.Recordset
    Dim SQL As String
    Dim nRecordcount As Long
    Dim sBranch() As String
    Dim sToken As String
    Dim n As Long
    Dim sContent As String
    Dim s As String
    
    ' Outputs All content in the Branch up to the Node being edited.
    
    ' This is convoluted.  There has to be a better way but this works.
    
    ' Typical Branch: 001_001_002_001
    
    ' Find the Content of 001, 001_001, 001_001_002, and 001_001_002_001
    
    ' E.g.
    
    ' 001 = "You come to a crossroad."
    ' 001_001 = "You decide to turn left.
    ' 001_001_002 = "You are stopped by a sharply dressed man."
    ' 001_001_002_001 = "He offers you a deal.
    ' skipping ahead...
    ' 001_001_002_001_001_003 = "YOU DIED!!"
    ' (make better choices)
    
    txtParent.Text = vbNullString
    
    sBranch = Split(cmbParent.Text, "_") ' Create an Array of Each Token in the string.
    
    If Not ArrayInitialized(sBranch) Then Exit Sub ' Nothing there.
    
    sToken = sBranch(0) ' First Token is a special case that doesn't contain an underscore.
    
    SQL = "SELECT Content FROM Branches WHERE Branch = '" & sToken & "'"
    
    nRecordcount = OpenRST(RST, SQL, idx_Recordset_Snapshot)
    If nRecordcount = 0 Then Exit Sub
    
    s = RST.Fields("Content") ' Save the string.
    
    For n = LBound(sBranch) + 1 To UBound(sBranch)
    
      sToken = sToken & "_" & sBranch(n) ' Now add in tokens one at a time separated by an underscore.
    
      SQL = "SELECT Content FROM Branches WHERE Branch = '" & sToken & "'"
    
      nRecordcount = OpenRST(RST, SQL, idx_Recordset_Snapshot)
    
      If nRecordcount > 0 Then 
    
          s = s & vbCrLf & RST.Fields("Content")
    
      end if
    Next n
    
    txtParent.Text = s
    
    End Sub
    Last edited by cafeenman; Apr 18th, 2024 at 04:25 AM.

  2. #2
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,263

    Re: Split a branch into nodes, reconstrucing and running a query on each = inefficien

    You should probably normalise your database so that you have a table with ParentId and ChildID for each node, and you can construct a full path (branch) from that, by simply 'walking' down the path from parent to child, and so on.

    e.g.

    ParentID ChildID
    1..............2
    1..............3
    2..............4

    This would be very simple with a recursive query, but I don't know if you can do that with DAO.
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  3. #3

    Thread Starter
    PowerPoster cafeenman's Avatar
    Join Date
    Mar 2002
    Location
    Florida
    Posts
    2,646

    Re: Split a branch into nodes, reconstrucing and running a query on each = inefficien

    I have no idea how to write a recursive query. It may or may not be possible but I wouldn't even know where to start.

    I'll ask my friends at the googles.

    Thanks.

    PS. I'm about to upload the "final" version in the Code Bank. It's been polished a bit and has some quality of life stuff added. Everything is working now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width