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