More Relationship Maddness

[PeteCresswell]

I'm creating some relationships via code.

End product looks like this: http://tinyurl.com/4ku6w5

I would prefer to have it look like this: http://tinyurl.com/3rj87y

I'm assuming that the two representations are functionally identical,
so it's not a religious issue, just a visual nice-to-have.

Code looks like this (with apologies in advance for the wrapping):
'
--------------------------------------------------------------------------------
' CREATE RELATIONSHIPS the TradingAccountProperty-related tables
' ---------------------------------------
' tblTradingAccountPropertyValue==tlkpTradingAccountPropertyType
on TradingAccountPropertyTypeID

1650 curName = "TradingAccountPropertyTypeID"
1651 Set curRelation =
CurrentDb.CreateRelation("tlkpTradingAccountPropertyTypetblTradingAccountPropertyValue",
"tlkpTradingAccountPropertyType", "tblTradingAccountPropertyValue")
1652 With curRelation
1653 .Fields.Append curRelation.CreateField(curName)
1654 .Fields(curName).ForeignName = curName
1655 End With
1659 targetDB.Relations.Append curRelation

' ---------------------------------------
'
tblTradingAccount==tblTradingAccountTradingAccountPropertyValue on
TradingAccountID

1660 curName = "TradingAccountID"
1661 Set curRelation =
CurrentDb.CreateRelation("tblTradingAccounttblTradingAccountTradingAccountPropertyValue",
"tblTradingAccount", "tblTradingAccountTradingAccountPropertyValue",
dbRelationLeft)
1662 With curRelation
1663 .Fields.Append curRelation.CreateField(curName)
1664 .Fields(curName).ForeignName = curName
1665 End With
1669 targetDB.Relations.Append curRelation

' ---------------------------------------
'
tblTradingAccountTradingAccountPropertyValue==tblTradingAccountPropertyValue
on TradingAccountPropertyTypeID

1670 curName = "TradingAccountPropertyTypeID"
1671 Set curRelation =
CurrentDb.CreateRelation("TradingAccountPropType",
"tblTradingAccountPropertyValue",
"tblTradingAccountTradingAccountPropertyValue", dbRelationDontEnforce)
1672 With curRelation
1673 .Fields.Append curRelation.CreateField(curName)
1674 .Fields(curName).ForeignName = curName
1675 End With
1679 targetDB.Relations.Append curRelation

' ---------------------------------------
'
tblTradingAccountTradingAccountPropertyValue==tblTradingAccountPropertyValue
on TradingAccountPropertyValueID

1680 curName = "TradingAccountPropertyValueID"
1681 Set curRelation =
CurrentDb.CreateRelation("TradingAccountPropValue",
"tblTradingAccountPropertyValue",
"tblTradingAccountTradingAccountPropertyValue", dbRelationDontEnforce)
1682 With curRelation
1683 .Fields.Append curRelation.CreateField(curName)
1684 .Fields(curName).ForeignName = curName
1685 End With
1689 targetDB.Relations.Append curRelation
'
--------------------------------------------------------------------------------

 

[PeteCresswell]

End product looks like this: http://tinyurl.com/4ku6w5

I would prefer to have it look like this: http://tinyurl.com/3rj87y

Click the little "All Sizes" icon to see a magnified version of each
picture.

 

[John Spencer]

In order to achieve what you want you need to add two sets of fields to the
one relationship. You are creating two separate relationships.

Sample function to do what you are trying to do.

Public Function xxx()
Dim relAny As Relation
Dim dbAny As Database

Set dbAny = CurrentDb()

Set relAny = dbAny.CreateRelation("FAQFAQXX", "FAQ", "FAQ0")
With relAny
.Fields.Append relAny.CreateField("FID")
.Fields("FID").ForeignName = "FID"
.Fields.Append relAny.CreateField("fSubject")
.Fields("fSubject").ForeignName = "fSubject"
End With
dbAny.Relations.Append relAny


End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

In order to achieve what you want you need to add two sets of fields to the
one relationship. You are creating two separate relationships.

1652 With curRelation
1653 .Fields.Append curRelation.CreateField(curName)
1654 .Fields(curName).ForeignName = curName

'Something like the following (going from memory)
.Fields.Append curRelation.CreateField(xxx)
.Fields(XXX).ForeignName = xxx

1655 End With

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[(PeteCresswell)]

Per John Spencer:

In order to achieve what you want you need to add two sets of fields to the
one relationship. You are creating two separate relationships.

Bingo. Worked like a charm.

Thanks.

So obvious..... in retrospect.... -)