Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table structure (MSACCESS2003)

    I am trying to put data into tables to mimic where the data is presently held. The data comes from a directory structure on a drive. Basically the data comes from word documents stored in various folders and subfolders. List1 will reference the main folder, Table2 will reference the subfolders of Table1 folders, and Table3 will be where the data goes.

    Before I try it, I can see that if I want to put data into Table3 from a folder in Table1, I won't be able to as there is no primary key being generated in Table2. Is that correct, and how do I get round it if its the case. Many thanks
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table structure (MSACCESS2003)

    I'd use a single table to store the folder structure, with a FolderID field and a ParentID field. The latter would be blank for a 'top' folder, and contain the FolderID of the parent folder in case of a subfolder.

    See (fake) diagram below.
    Attached Images Attached Images
    • File Type: png x.png (2.0 KB, 2 views)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table structure (MSACCESS2003)

    Many thanks Hans, clever fake drawing, don't know how you worked that magic. Its a good idea, however I did not want to mix Folders and subfolders in one table. I wanted to end up having a form with listboxes on, List1 would reflect the main folder name (being a sport), and would produce a list of documents in that folder, and a second listbox which would show the subfolders held in List1 selected folder. Clicking on the subfolders list would produce those documents in the subfolder.

    Maybe when the start of filling in the tables is done, if there was no subfolder of a folder, a dummy entry could be put in Table2 (ie the word No Subfolder) so it generated a primary number? The table filling will be done in VBA Thanks, regarda

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table structure (MSACCESS2003)

    You can create a query based on tblFolders that selects records for which ParentID is null. This is the list of main folders; you can use this as row source for the first list box.
    You can create another query based on tblFolders that selects records for which ParentID equals the FolderID selected in the first list box. This is the list of subfolders; you can use it as row source for the second list box.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table structure (MSACCESS2003)

    Thanks Hans. Maybe it's because I had a late night last night, but I am trying to understand your joins in your drawing. Would be greatful if you could clarify my primary and foreign keys, sorry to be stupid. Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table structure (MSACCESS2003)

    See the attached sample database.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table structure (MSACCESS2003)

    Many, many thanks for taking the time doing that for me. That answers it in shot. Have a good weekend and thanks again.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table structure (MSACCESS2003)

    Another option would be to use a TreeView control to display the folder structure, but that would be more work since that is not a native Access control, so you need to do everything in code.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table structure (MSACCESS2003)

    I am doing something stupid somewhere. I have a form that uses scripting to go through a bunch of directories and subdirectories filling a set of 4 listboxes.

    List3 fills with a list of directory names (Sport Name)
    List3b fills with a list of subdirectories of the directories listed in List3
    List3c fills with the document names stored within the main directories listed in List3
    List3d fills with a list of documents held within the subdirectories held in List3b

    I am using a conveniently built form that I had to sequentially cycle through the lists to fill tables.

    Using the framework of Hans attachment it all works after the tables are filled, except for the last data not appearing, ie when lbxSubSport is clicked no data appears in lbxSubData.

    Can anyone spot where my code is incorrect between List3b and List3d for this to happen. Appologies for the method being crude, it utilises bits already made. Thanks


    For TP1 = 0 To Me.List3.ListCount - 1 ' This code runs through parent directory
    Me.List3.SetFocus
    Me.List3.ListIndex = TP1

    ' ================================================== ========
    ' PUT PARENT DIRECTORY IN
    SQL = "Select * From tblSports" ' This code puts parent directory name (Sport) into tblSports
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
    rst.AddNew
    rst![sPort] = Me.Text57
    PK = rst![SportID] ' This is the tblSports Primary Key number (Logged to go into tblData)
    rst.Update
    ' ================================================== ========
    ' PUT IN FOLDERS CONTAINED WITHIN PARENT DIRECTORY
    For TP2 = 0 To Me.List3c.ListCount - 1 ' This code runs through data in parent directory
    Me.List3c.SetFocus
    Me.List3c.ListIndex = TP2

    SQL2 = "Select * From tblData" ' This code puts parent directory name (Sport) into tblSports
    Set rst2 = db.OpenRecordset(SQL2, dbOpenDynaset)
    rst2.AddNew
    rst2![Data] = Me.FR
    rst2![SportID] = PK
    rst2.Update
    Next
    ' ================================================== ========
    ' DEAL WITH SUBDIRECTORIES

    If Me.List3b.ListCount > 0 Then

    ' Loop subdirecotories
    For TP3 = 0 To Me.List3b.ListCount - 1 ' This code runs through subdirectories
    Me.List3b.SetFocus
    Me.List3b.ListIndex = TP3

    ' PUT SUBFOLDER SPORT INTO tblSports
    SQL = "Select * From tblSports"
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
    rst.AddNew
    rst![sPort] = Me.List3b.Value
    rst![ParentID] = PK
    rst.Update

    ' PUT SUBFOLDER DATA INTO tblData
    SQL2 = "Select * From tblData"
    Set rst2 = db.OpenRecordset(SQL2, dbOpenDynaset)

    For TP4 = 0 To Me.List3d.ListCount - 1 ' This code runs through data in subdirectory
    Me.List3d.SetFocus
    Me.List3d.ListIndex = TP4
    rst2.AddNew
    rst2![SportID] = PK
    rst2![Data] = Me.List3d.Value
    rst2.Update
    Next
    Next
    End If
    Next


    Set rst = Nothing
    Set rst2 = Nothing

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table structure (MSACCESS2003)

    I'm afraid I don't understand your code, but it looks like you're opening recordsets on tblSports and tblData repeatedly without closing them in between; this could cause problems. You should open each recordset once, before the For loops, and close them after finishing the loops.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table structure (MSACCESS2003)

    Thanks Hans, sorry for confusion. Basically I am still going round in circles trying to see where my problems lay.

    I have a form that starts a search within a directory getting a list of all word documents. Some of these are within a directory and some within a subdirectory. It all works okay and fills four lists. 1 list (top letf) contains the directory names which are sport names. The list on the top right is filled with documents found within that directory selected on the left list.

    The list on the bottom left is filled with subdirectory names related to the selected directory (Top left List)
    The list on the bottom right contain the documents within the selected subdirectory of that subdirectory selected on the bottom left.

    What I am trying to do is programatically put this data into logical tables following your suggestion but cannot find an easy way which works.
    I was trying to cycle through the listboxes, but wondering if there is an easier way? It may be too complex and I will have to work on it more. Thanks
    Attached Images Attached Images

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table structure (MSACCESS2003)

    I have no idea what the code that you posted higher up in this thread has to do with this all, so I cannot help, sorry.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table structure (MSACCESS2003)

    No problem, thanks and best regards

Posting Permissions

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