Adding and Deleting Primary Keys W/ Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
Brendan said:
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.
Click to expand...

Alternatively use the CONSTRAINT syntax e.g.

Sub Test
With CurrentProject.Connection
.Execute _
"ALTER TABLE Test DROP CONSTRAINT PrimaryKey;"
.Execute _
"ALTER TABLE Test ADD CONSTRAINT" & _
" pk__test PRIMARY KEY (col1, col2);"
End With
End Sub

Jamie.

--
 
Back
Top