DistinctCount Property

Applies To

Index Object.

Description

Returns a value that indicates the number of unique values for the Index object that are included in the associated table.

Return Values

This property returns a Long data type.

Remarks

Check the DistinctCount property to determine the number of unique values, or keys, in an index. Any key is counted only once, even though there may be multiple occurrences of that value if the index permits duplicate values. This information is useful in applications that attempt to optimize data access by evaluating index information. The number of unique values is also known as the cardinality of an Index object.

The DistinctCount property won't always reflect the actual number of keys at a particular time. For example, a change caused by a transaction rollback won't be reflected immediately in the DistinctCount property. The number will be accurate immediately after using the CreateIndex method.

Example

This example checks the DistinctCount property of an Index of a TableDef object opened on a table in the database.

Dim dbsNorthwind As Database, tdfCustomers As TableDef
Dim idxCurrent As Index
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set tdfCustomers = dbsNorthwind!Customers
Set idxCurrent = tdfCustomers.Indexes(0)
Debug.Print idxCurrent.DistinctCount

Example (Microsoft Access)

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 using the CompactDatabase method.

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

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    ' Return TableDef object pointing to Orders table.
    Set tdf = dbs.TableDefs!Orders
    ' Create new index.
    Set idx = tdf.CreateIndex("OrderIDDate")
    ' Create and append index fields.
    Set fldOrderID = idx.CreateField("OrderId", dbLong)

    Set fldOrderDate = idx.CreateField("OrderDate", dbDate)
    idx.Fields.Append fldOrderID
    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
End Sub