Create Primary key in access table with VB

[nhidalgo]

I am using the following code to create an access db. I would like to make the field "key" a primary key.
Thanks for any help.
Nick

Set db = DBEngine.CreateDatabase("f:\bread\config\breaddb.mdb", dbLangGeneral)
Set td = New TableDef
Set f = td.CreateField("key", dbText, 50)
td.Fields.Append f
Set f = td.CreateField("upc", dbDouble, 15)
td.Fields.Append f
Set f = td.CreateField("quan", dbDouble, 6)
td.Fields.Append f
Set f = td.CreateField("scantype", dbText, 2)
td.Fields.Append f
Set f = td.CreateField("day", dbText, 3)
td.Fields.Append f
Set f = td.CreateField("importdate", dbDouble, 12)
td.Fields.Append f
Set f = td.CreateField("weekendate", dbDouble, 12)
td.Fields.Append f
td.Name = "BreadControl"
db.TableDefs.Append td
Set db = Nothing
MsgBox "Database build is complete", vbOKOnly

 

[Schroeder]

You can create an index, add the "key" field to it, set it as Unique and Primary, then add it to your table

Set idxPK = td.CreateIndex("PrimaryKey")
With idxPK
    .Fields.Append(.CreateField("key", dbText, 50)
    .Unique = True
    .Primary = True
End With
td.Indexes.Append idxPK

Or, you can make the whole thing easier like this

db.Execute "CREATE TABLE BreadControl (key Text(50), upc Double, quan Double, scantype Text(2), day Text(3), importdate Double, weekendate Double, PRIMARY KEY (key));"

 

[Schroeder]

OK, "key" is a reserved word.
If the field really must be named key (which will probably cause headaches throughout your project)

db.Execute "CREATE TABLE BreadControl ([key] Text(50), upc Double, quan Double, scantype Text(2), day Text(3), importdate Double, weekendate Double, PRIMARY KEY ([key]));"