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