Foreign Key Already Exists

[martialarts]

Hi. I am programatically creating an access database and 2 of the tables have the same foreign key that relates to a primary key of a third table. When I try to programatically generate the second foreign key I get the error: "...Object 'ElementID' already exists." Here is the relevant part of my code:

       With tbl1 'creates table that contains primary key
           .Name = "Elements"
           .Columns.Append("ElementID", ADOX.DataTypeEnum.adInteger)
           .Columns.Append("Name", ADOX.DataTypeEnum.adVarWChar)
           .Columns.Append("GenDesc", ADOX.DataTypeEnum.adLongVarWChar)
           .Columns.Append("HLValue", ADOX.DataTypeEnum.adDouble)
           .Columns.Append("HHValue", ADOX.DataTypeEnum.adDouble)
           .Columns.Append("CLValue", ADOX.DataTypeEnum.adDouble)
           .Columns.Append("CHValue", ADOX.DataTypeEnum.adDouble)
           .Keys.Append("ElementID", ADOX.KeyTypeEnum.adKeyPrimary, "ElementID")
           .Keys.Append("Name", ADOX.KeyTypeEnum.adKeyUnique, "Name")
       End With

       With tbl3 'creates table that contains first foreign key
           .Name = "Tests"
           .Columns.Append("TestID", ADOX.DataTypeEnum.adInteger)
           .Columns.Append("ElementNumber", ADOX.DataTypeEnum.adInteger)
           .Columns.Append("ElementID", ADOX.DataTypeEnum.adInteger)
           .Keys.Append("ElementID", ADOX.KeyTypeEnum.adKeyForeign, "ElementID", "Elements", "ElementID")
           .Keys.Append("TestID", ADOX.KeyTypeEnum.adKeyForeign, "TestID", "TestList", "TestID")
       End With
       cat.Tables.Append(tbl3)

       With tbl7 'creates table that contains second foreign key that causes problem
           .Name = "Advanced"
           .Columns.Append("AdvancedID", ADOX.DataTypeEnum.adInteger)
           .Columns.Append("ElementID", ADOX.DataTypeEnum.adInteger)
           .Columns.Append("Problem", ADOX.DataTypeEnum.adVarWChar)
           .Keys.Append("AdvancedID", ADOX.KeyTypeEnum.adKeyForeign, "AdvancedID", "AdvancedList", "AdvancedID")
           .Keys.Append("ElementID", ADOX.KeyTypeEnum.adKeyForeign, "ElementID", "Elements", "ElementID") 'PROBLEM OCCURS HERE
       End With
       cat.Tables.Append(tbl7)

What is wrong with this? Thanks a lot!

[pelikan]

the error message says it all: you can't have 2 key definitions in the db with the

same name, give them different names. (here - ElementID is ambiguous)

[martialarts]

You were right on. I didn't realize that parameter needed to be unique. Thanks a lot!