'Linking Subdatasheets to Main Table

I have a main table and multiple other tables for which I want to link each one to a row of the main table.

The main table "Data" consists of columns (Name, x, y) where "Name" is the primary key and all values are unique.

Each of the other tables have columns (Name2, z) where the value of "Name2" is the same in each row and also corresponds to the table name.

I want to make each table a subdatasheet of "Data" where each row in "Data" shows the values in the table corresponding to its name. (i.e. where Name = Name2)

Below is what I have so far, but the code doesn't work because linkchildfields and linkmasterfields need to be run from a subform.(?) The error I am getting is 'Property Not Found' once the code reached the linkchildfields line.

I will be the only user of the Database. Put simply, I am looking for a way to automate the process of setting one table as a Subdatasheet of another. I could do this manually directly from the access table, but I want to automate it for when a new table is created and needs to be set as a subdatasheet of the main table.

Thanks.

Sub STS()

Dim i As TableDef
Dim db As Database
Dim tbl As TableDef

Set db = CurrentDb()
Set tbl = db.TableDefs("Data")

For Each i In db.TableDefs
    If Left$(i.Name, 4) <> "MSys" Or i.Name <> "Data" Then
        tbl.Properties("SubdatasheetName") = i.Name
        tbl.Properties("LinkChildFields") = "Name2"
        tbl.Properties("LinkMasterFields") = "Name"
    End If
Next
End Sub
sqlms-accessms-access-2007vba


Solution 1:[1]

Why are you trying to do this with code? This functionality is built into Access via the concept of Relationships, and it literally takes 2 minutes to set up for any new table you might add. Take a look at these documents to see how it's done, both of which were penned by Microsoft personnel:

Create, edit or delete a relationship

How to define relationships between tables in an Access database

Solution 2:[2]

To late, but since I had this problem with linked tables and this functionality would help on queries.

If you don't have the attribute you have to create it.

I use this code to add subdatasheetName to linked sql tables

use:
    TBSetAttrib "TableName", "SubdatasheetName", "subtable"
    TBSetAttrib "TableName", "LinkChildFields", "subTableLinkID"
    TBSetAttrib "TableName", "LinkMasterFields", "masterTableLinkID"


Sub TBSetAttrib(tbName As String, tbAttribute As String, tbValue As String)
    Dim DB As DAO.Database
    Set DB = CurrentDb
    Dim tb As TableDef, p As Property
    Set tb = DB.TableDefs(tbName)

    On Error GoTo noAttribTBSetAttrib
    tb.Properties(tbAttribute).Value = tbValue
    
saidaTBSetAttrib:
    Debug.Print tbName; " "; tbAttribute; ": " & tb.Properties(tbAttribute).Value
    Set DB = Nothing
    Exit Sub
noAttribTBSetAttrib:
    Dim np As Property
    Set np = tb.CreateProperty(tbAttribute, dbText, tbValue)
    tb.Properties.Append np
    GoTo saidaTBSetAttrib
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Johnny Bones
Solution 2

Related Questions