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 (Microsoft Jet workspaces only).

Return Values

The return value is 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 rolled back transaction won't be reflected immediately in the DistinctCount property. The DistinctCount property value also may not reflect the deletion of records with unique keys. The number will be accurate immediately after you use the CreateIndex method.

Example

This example uses the DistinctCount property to show how you can determine the number of unique values in an Index object. However, this value is only accurate immediately after creating the Index. It will remain accurate if no keys change, or if new keys are added and no old keys are deleted; otherwise, it will not be reliable. (If this procedure is run several times, you can see the effect on the DistinctCount property values of the existing Index objects.)

Sub DistinctCountX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim idxCountry As Index
    Dim idxLoop As Index
    Dim rstEmployees As Recordset
    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind!Employees

    With tdfEmployees
        ' Create and append new Index object to the Employees
        ' table.
        Set idxCountry = .CreateIndex("CountryIndex")
        idxCountry.Fields.Append _
            idxCountry.CreateField("Country")
        .Indexes.Append idxCountry

        ' The collection must be refreshed for the new
        ' DistinctCount data to be available.
        .Indexes.Refresh

        ' Enumerate Indexes collection to show the current
        ' DistinctCount values.
        Debug.Print "Indexes before adding new record"
        For Each idxLoop In .Indexes
            Debug.Print "    DistinctCount = " & _
                idxLoop.DistinctCount & ", Name = " & _
                idxLoop.Name
        Next idxLoop

        Set rstEmployees = _
            dbsNorthwind.OpenRecordset("Employees")

        ' Add a new record to the Employees table.
        With rstEmployees
            .AddNew
            !FirstName = "April"
            !LastName = "LaMonte"
            !Country = "Canada"
            .Update
        End With

        ' Enumerate Indexes collection to show the modified
        ' DistinctCount values.
        Debug.Print "Indexes after adding new record and " & _
            "refreshing Indexes"
        .Indexes.Refresh
        For Each idxLoop In .Indexes
            Debug.Print "    DistinctCount = " & _
                idxLoop.DistinctCount & ", Name = " & _
                idxLoop.Name
        Next idxLoop

        ' Delete new record because this is a demonstration.
        With rstEmployees
            .Bookmark = .LastModified
            .Delete
            .Close
        End With
        ' Delete new Indexes because this is a demonstration.
        .Indexes.Delete idxCountry.Name
    End With

    dbsNorthwind.Close

End Sub

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 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