Creating relationships using VBA

[Gandalf]

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 CreateRelations hip()

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 = "theChildTa ble"
strRelName = "myRelationship "
strFTKey = "foreignTableKe y"
strRelatedTbl = "parentTabl e"
strRTKey = "parentTableKey "

Set catDB = New ADOX.Catalog
catDB.ActiveCon nection = CurrentProject. Connection

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

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

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

End Function

[Lyle Fairfield]

CurrentProject. Connection.Exec ute _
"ALTER TABLE Classes " _
& "ADD CONSTRAINT ClassSchool " _
& "FOREIGN KEY (SchoolID) " _
& "References Schools (SchoolID)"

Gandalf wrote:

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).

[Alan Webb]

Gandalf,
I'm with Lyle. I'd write either a CREATE TABLE statement with my
constraints declared in it or an ALTER TABLE statement to change the table
design.
--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS

"Gandalf" <Ga***********@ gmail.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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 CreateRelations hip()

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 = "theChildTa ble"
strRelName = "myRelationship "
strFTKey = "foreignTableKe y"
strRelatedTbl = "parentTabl e"
strRTKey = "parentTableKey "

Set catDB = New ADOX.Catalog
catDB.ActiveCon nection = CurrentProject. Connection

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

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

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

End Function