Threaded List to Flat File Programming Puzzle 1

[MarkRobinson]

Well, I've been working on this for days, unable to figure out the program logic. Maybe I'm getting old.

I imported my message board into access. I've got 32,000 messages each identified with a
MSG#: 24 - Number of this message
PREVIOUS#: 23 - If any. Empty means top of thread
NEXT#: 27 28 39 42 - Replies to this message
MSGINFO: Test of message, etc

I want to make a single page of each thread and I don't see a good method. Following the tree down with unlimited branches seems like a very subtle loop and, like a squirrel with a nut, I'm getting lost in the branches.

1) Get all Top Threads (PREVIOUS is blank)
2) For Each Top Thread
3) Get First NEXT#
4) Do until NEXT# is blank
5) For Each NEXT#
6) Do Until NEXT# is blank
7) How far down the rabbit hole do I want to go?

So I'm looking for logic - or a suggested path. Some ideas I've beem mulling...

Create a separate table for each thread.
Create a field in each message that identifies its thread level.
Create a separate one-to-many table with MSG# and NEXT#

Advice will be appreciated!

Mark Robinson

http://www.HandicappedPets.net

 

[Golom]

Looks like recursion may work. Here's a simple example of a recursive routine to list all the files in a folder and in all it's contained sub-folders. You may be able to adapt it to your case.

Public Sub DirTree(DirPath As String, nH As Integer, n As Integer)
Dim fldr                        As Folder
Dim sfldr                       As Folder
Dim File                        As File
Print #nH,
Print #nH, Space(3 * n) & DirPath
With New FileSystemObject
    Set fldr = .GetFolder(DirPath)
    [COLOR=black cyan]' Print the files in the folder[/color]
    For Each File In fldr.Files
        Print #nH, Space(3 * n) & File.Name
    Next
    [COLOR=black cyan]' Recursively process each sub-folder[/color]
    For Each sfldr In fldr.SubFolders
        DirTree sfldr.Path, nH, n + 1
    Next
End With
End Sub

and you would call it with

Private Sub Command31_Click()
Dim nH                          As Integer
nH = FreeFile
Open "DirTree.txt" For Output As #nH
DirTree "C:\myFolder", nH, 0
Close #nH
ExecuteWait "Dirtree.txt"
End Sub

"ExecuteWait" is just a utility routine that I use to display a txt file with notepad.

 

[MichaelRed]

bespeaks of de-normalized data. Each REPLY should be a seperate record. Folow the philosphy: Deep is better than Wide. e.g. having to parse the next field to get the seperat items is more work (at least for you) than a simple query which uses "Msg #" to retrieve all. To get more elaborate, make it a PARAMETER query with "Msg #" as the parameter and sort by the (normalized) "Next".




MichaelRed