Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Unanswered: Access and sharepoint 2010 Parent - Child - Grandchild linked lists

    Hello Everyone,


    I have grown fond of utilizing Microsoft Access 2010 and SharePoint 2010 Enterprise edition to develop fast and effective applications which can be enhanced by Excel's awesome pivot tables for reporting.

    I am aware of the Parent - Child relationship which can be established using lookup fields.

    I am trying to understand if it is possible to create a Parent - Child - Grandchild relationship which can be utilized within Access 2010? the Problem I run into is the Grandchild relationship. I need to be able to see all fields in one table to export into Excel. Right now I can not see the grandchildren.


    Can some one let me know if this is even possible and if so any suggestions?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The most usual way to implement a liked list in an Acess table consist of using two columns: one is an Identity column (Autonumber), let's call it ID and the second works as a Foreign Key column, except that its values (the keys) refer to the ID column of the same table instead of referring to a colum in another table, let's call it P_ID. When P_ID is Null or equals zero (depending on how the table was defined), the row is a parent. When P_ID contains a number greater than zero, the row is a child and it's P_ID value points to the ID value of its parent.

    The class Cls_LinkedList in the database in the atached file handles such linked list tables. It has 3 initialization properties:
    - TableName that allows to specify the name of the table to be processed.
    - PKName that allowws to specify the name of the ID column.
    - FKName that allows to specify the name of the P_ID column.

    The class Cls_LinkedList has 4 methods that return data from the table, data based on a value from the ID column:
    - GetQryChild returns the name of a query that the method creates then it is called. It receives the ID value of a parent row and the query contains that parent row and all the child rows.
    - GetQryParent does the same as GetQryChild but it receives the ID value of a child row and returns the name of a query that contains all the parent rows.
    - GetRstChild returns a DAO Recordset that is populated with all the parent rows for an ID value.
    - GetRstParent returns a DAO Recordset that is populated with all the child rows for an ID value.

    The class can be used easily:
    Code:
    Sub Test_GetQuery()
    
        Dim cls As Cls_LinkedList
        
        Set cls = New Cls_LinkedList
        With cls
            .TableName(False) = "Tbl_List"
            .PKName = "ID"
            .FKName = "P_ID"
            DoCmd.OpenQuery .GetQryParent(RowID:=29, Persistent:=True)
        End With
        Set cls = Nothing
        
    End Sub
    The database in the attachment also contains a module that allows to test the class. It has a procedure to create a sample table and two procedures to test the class one using a Recordset and one using a query.

    The other file in the attachment is a pdf document that describes the properties and methods of the class Cls_LinkedList with their arguments.
    Attached Files Attached Files
    Have a nice day!

Tags for this Thread

Posting Permissions

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