Adding and Deleting Primary Keys W/ Code

M

MDW

Hey all.

I'm trying to create and delete primary key sequences (whcih often will
include more than one field) in Access 2000 using VBA. However, the Help
isn't very helpful about this, and the code I did try returns an error to the
effect of "The requested object is not valid" or something like that.

The table and field names will be constant.

Thanks.
 
B

Brendan Reynolds

Public Sub CreateIndex()

CurrentDb.Execute "DROP INDEX PrimaryKey ON tblTest"
CurrentDb.Execute "CREATE INDEX PrimaryKey on tblTest (TestText,
TestNumber) WITH PRIMARY"

End Sub

See 'CREATE INDEX' and 'DROP' topics in the help system for details.
 
J

JaRa

Other Solution :

Dim idx As DAO.Index

Set idx =
currentdb.TableDefs("TableName").CreateIndex("PrimaryKey")
idx.Fields.Append idx.CreateField("FieldName1")
idx.Fields.Append idx.CreateField("FieldName2")
' to be set as required
idx.Clustered = true or false
idx.IgnoreNulls = true or false
idx.Primary = true or false
idx.Required = true or false
idx.Unique = true or false

CurrentDb.TableDefs("TableName").Indexes.Append idx
Set idx = Nothing
 
Top