This example demonstrates the PrimaryKey and Unique properties of an Index. The code creates a new table with two columns. The PrimaryKey and Unique properties are used to make one column the primary key for which duplicate values are not allowed.
Sub PrimaryKeyX()
Dim catNorthwind As New ADOX.Catalog
Dim tblNew As New ADOX.Table
Dim idxNew As New ADOX.Index
Dim idxLoop As New ADOX.Index
Dim colLoop As New ADOX.Column
' Connect the catalog
catNorthwind.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=c:\Program Files\" & _
"Microsoft Office\Office\Samples\Northwind.mdb;"
' Name new table
tblNew.Name = "NewTable"
' Append a numeric and a text field to new table.
tblNew.Columns.Append "NumField", adInteger, 20
tblNew.Columns.Append "TextField", adVarWChar, 20
' Append new Primary Key index on NumField column
' to new table
idxNew.Name = "NumIndex"
idxNew.Columns.Append "NumField"
idxNew.PrimaryKey = True
idxNew.Unique = True
tblNew.Indexes.Append idxNew
' Append an index on Textfield to new table.
' Note the different technique: Specifying index and
' column name as parameters of the Append method
tblNew.Indexes.Append "TextIndex", "TextField"
' Append the new table
catNorthwind.Tables.Append tblNew
With tblNew
Debug.Print tblNew.Indexes.Count & " Indexes in " & _
tblNew.Name & " Table"
' Enumerate Indexes collection.
For Each idxLoop In .Indexes
With idxLoop
Debug.Print "Index " & .Name
Debug.Print " Primary key = " & .PrimaryKey
Debug.Print " Unique = " & .Unique
' Enumerate Columns collection of each Index
' object.
Debug.Print " Columns"
For Each colLoop In .Columns
Debug.Print " " & colLoop.Name
Next colLoop
End With
Next idxLoop
End With
' Delete new table as this is a demonstration
catNorthwind.Tables.Delete tblNew.Name
Set catNorthwind = Nothing
End Sub