>I have the following code which creates a table in a database and creates a primary key. I am also trying to create a foreign key which is made up of multiple fields to be used later in the program to do a SEEK. The program runs through the code, creates the table and primary key OK but does not seem to create the Foreign key. What am I doing wrong?
>
>>Set tblDef = db2.CreateTableDef("RankFacts")
>
>>Set fldDef = tblDef.CreateField("RecId", dbLong)
>> fldDef.Attributes = dbAutoIncrField
>> tblDef.Fields.Append fldDef
>>Set fldDef = tblDef.CreateField("PharmID", dbText, 15)
>> tblDef.Fields.Append fldDef
>>Set fldDef = tblDef.CreateField("AuditFact", dbText, 45)
>> tblDef.Fields.Append fldDef
>>Set fldDef = tblDef.CreateField("Rank", dbLong)
>> tblDef.Fields.Append fldDef
>>Set fldDef = tblDef.CreateField("Mark", dbText)
>> tblDef.Fields.Append fldDef
>>
>> 'create indexes on the ID field.
>> Set idx = tblDef.CreateIndex("Rec ID")
>> Set fldDef = idx.CreateField("RecID")
>> idx.Primary = True
>> idx.Fields.Append fldDef
>> 'add the the Players table
>> tblDef.Indexes.Append idx
>>
>> Dim fldDef2 As Field
>>
>>'Foreign key
>> Set idx = tblDef.CreateIndex("PharmIDFact")
>> Set fldDef = idx.CreateField("PharmID")
>> Set fldDef2 = idx.CreateField("AuditFact")
>> idx.Fields.Append fldDef
>> idx.Fields.Append fldDef2
>> tblDef.Indexes.Append idx
>> tblDef.Indexes.Refresh
>
>> 'add the finished table to the database
>> db2.TableDefs.Append tblDef
Becky,
You can not setup Foreign Key in this way. A Foreign Key is part of a relationship between two tables. When a relationship is setup between two tables, two indexes are created, one index on Table1 and one index on Table2 whick is call a Foreign Key. This does assume that you are using an Access Database.
I hope this helps.