I'm trying to modify the sample macro, 'Name Foreign Constraints', to include the attribute name from the child entity in the relationship. I'm having some trouble pulling that property from the relationship. My target naming convention is:
FK_<relation.ParentEntity.TableName>_<relation.ChildEntity.TableName>_<For a given relationship, Column Name of attribute with the FK constraint in the child entity>
Based on my understanding of the documentation I tried the following approach, which returns "Object var is 'nothing'.":
For Each relation In mdl.Relationships
ParTable = relation.ParentEntity.TableName
chiTable = relation.ChildEntity.TableName
chiattr = relation.FKColumnPairs.Item(relation.ID).ChildAttribute.ColumnName
I am not fluent in basic, so this may just be bad syntax. Would appreciate any pointers.
I don't believe the relation.ID is the index to the FKColumnPairs.Item property. What I've done for this is iterate through the FKColumnPairs to put the child attribute names in a string, as in the below code snippet. If you just want the first child, you can exit out of the loop and remove the part that concatenates with a comma...
Dim oFKColPair As FKColumnPair
Dim sChildCols as String
For Each oFKCol In oRel.FKColumnPairs
If oFKCol.ChildAttribute.NullOption = "NULL" And oRel.OptionalityValue <> 0 Then
If sChildCols<>"" Then sChildCols = sChildCols & "," & vbNewLine & oFKCol.ChildAttribute.RoleName Else sChildCols = oFKCol.ChildAttribute.RoleName
That worked perfectly! As you said, I ended up removing the concatenation step, and changed some of the criteria to capture optional relationships. Thanks!