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