PrimaryKey and Unique Properties Example (VB)

PrimaryKey and Unique Properties Example (VB)

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