By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,092 Members | 1,597 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,092 IT Pros & Developers. It's quick & easy.

Table relationships and join type

P: n/a
I'm creating relationships between tables using VBA and ADOX. I can
create one-to-one relationships with an inner join, but I can't figure
out how to create these relationships with an outer join (specifically
a left outer join). I'm including the code that creates the
relationships with the inner join. Any help or suggestions would be
greatly appreciated.

Thanks!

<----- CODE FOLLOWS ----->

Function CreateRelationship()

Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim key As ADOX.key

Dim strForeignTbl As String
Dim strRelName As String
Dim strFTKey As String
Dim strRelatedTbl As String
Dim strRTKey As String

strForeignTbl = "theChildTable"
strRelName = "myRelationship"
strFTKey = "foreignTableKey"
strRelatedTbl = "parentTable"
strRTKey = "parentTableKey"

Set catDB = New ADOX.Catalog
catDB.ActiveConnection = CurrentProject.Connection

Set key = New ADOX.key
With key
.Name = strRelName
.RelatedTable = strRelatedTbl
.Type = adKeyForeign
.Columns.Append strFTKey
.Columns(strFTKey).RelatedColumn = strRTKey
End With

Set tbl = New ADOX.Table
Set tbl = catDB.Tables(strForeignTbl)
tbl.Keys.Append key

Set key = Nothing
Set tbl = Nothing
Set catDB = Nothing

End Function

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
To the best of my knowledge, relationships don't use outer joins, jsut
cascading updates and deletes and enforcing referential integrigty. Outer
joins are only used in queries.

Matthew Wells
MW****@FirstByte.ent

"Gandalf" <Ga***********@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
I'm creating relationships between tables using VBA and ADOX. I can
create one-to-one relationships with an inner join, but I can't figure
out how to create these relationships with an outer join (specifically
a left outer join). I'm including the code that creates the
relationships with the inner join. Any help or suggestions would be
greatly appreciated.

Thanks!

<----- CODE FOLLOWS ----->

Function CreateRelationship()

Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim key As ADOX.key

Dim strForeignTbl As String
Dim strRelName As String
Dim strFTKey As String
Dim strRelatedTbl As String
Dim strRTKey As String

strForeignTbl = "theChildTable"
strRelName = "myRelationship"
strFTKey = "foreignTableKey"
strRelatedTbl = "parentTable"
strRTKey = "parentTableKey"

Set catDB = New ADOX.Catalog
catDB.ActiveConnection = CurrentProject.Connection

Set key = New ADOX.key
With key
.Name = strRelName
.RelatedTable = strRelatedTbl
.Type = adKeyForeign
.Columns.Append strFTKey
.Columns(strFTKey).RelatedColumn = strRTKey
End With

Set tbl = New ADOX.Table
Set tbl = catDB.Tables(strForeignTbl)
tbl.Keys.Append key

Set key = Nothing
Set tbl = Nothing
Set catDB = Nothing

End Function

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.