Count Property

Applies To Connections collection, Containers collection, Databases collection, Documents collection, Errors collection, Fields collection, Groups collection, Indexes collection, Parameters collection, Properties collection, QueryDefs collection, Recordsets collection, Relations collection, TableDefs collection, Users collection, Workspaces collection.

Description

Returns the number of objects in a collection.

Return Value

The return value is an Integer data type.

Remarks

Because members of a collection begin with 0, you should always code loops starting with the 0 member and ending with the value of the Count property minus 1. If you want to loop through the members of a collection without checking the Count property, you can use a For Each...Next command.

The Count property setting is never Null. If its value is 0, there are no objects in the collection.

See Also Append method, Delete method, Refresh method.

Example

This example demonstrates the Count property with three different collections in the Northwind database. The property obtains the number of objects in each collection, and sets the upper limit for loops that enumerate these collections. Another way to enumerate these collections without using the Count property would be to use For Each...Next statements.

Sub CountX()

    Dim dbsNorthwind As Database
    Dim intloop As Integer

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind
        ' Print information about TableDefs collection.
        Debug.Print .TableDefs.Count & _
            " TableDefs in Northwind"
        For intloop = 0 To .TableDefs.Count - 1
            Debug.Print "    " & .TableDefs(intloop).Name
        Next intloop

        ' Print information about QueryDefs collection.
        Debug.Print .QueryDefs.Count & _
            " QueryDefs in Northwind"
        For intloop = 0 To .QueryDefs.Count - 1
            Debug.Print "    " & .QueryDefs(intloop).Name
        Next intloop

        ' Print information about Relations collection.
        Debug.Print .Relations.Count & _
            " Relations in Northwind"
        For intloop = 0 To .Relations.Count - 1
            Debug.Print "    " & .Relations(intloop).Name
        Next intloop

        .Close
    End With

End Sub

Example (Microsoft Access)

The following example prints the names and total number of fields in an Orders table:

Sub CountFields()
    Dim dbs As Database, tdf As TableDef
    Dim fld As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Orders table.
    Set tdf = dbs.TableDefs!Orders
    ' Count fields in Fields collection of TableDef object.
    Debug.Print tdf.Fields.Count
    ' List names of all fields.
    For Each fld In tdf.Fields
        Debug.Print fld.Name
    Next fld
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example displays the number of recordsets in the Nwindex.mdb database and then enters their names on Sheet1.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, i As Integer
Sheets("Sheet1").Activate
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Cells(1, 1).Value = "TableDef list for " & db.Name
Cells(1, 1).EntireColumn.AutoFit
For i = 0 To db.TableDefs.Count - 1
    Cells(i + 2, 1).Value = db.TableDefs(i).Name
Next i
MsgBox "There are " & db.TableDefs.Count & " TableDefs"
db.Close