DistinctCount Property Example (MDB)

The following example prints the number of unique values in the indexes on the OrderID and OrderDate fields of an Orders table. Note that the DistinctCount property is guaranteed to return the number of unique values in an index only immediately after an index has been created with the CreateIndex method, or after a database has been compacted or converted by using the CompactDatabase method.

Sub CountKeys()
    Dim dbs As Database, tdf As TableDef
    Dim idx As Index, fldOrderID As Field, fldOrderDate As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Orders table.
    Set tdf = dbs.TableDefs!Orders
    ' Create new index.
    Set idx = tdf.CreateIndex("OrderDateIndex")
    ' Create and append index fields.
    Set fldOrderDate = idx.CreateField("OrderDate", dbDate)
    idx.Fields.Append fldOrderDate
    ' Append new index.
    tdf.Indexes.Append idx
    ' Refresh Indexes collection.
    tdf.Indexes.Refresh
    ' Print value of DistinctCount property for new index.
    Debug.Print idx.DistinctCount
    Set dbs = Nothing
End Sub