Refresh Method

Applies To

Connections 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

Updates the objects in a collection to reflect the current database's schema.

Syntax

collection.Refresh

The collection placeholder is an object variable that represents a persistent collection.

Remarks

You can't use the Refresh method with collections that aren't persistent, such as Connections, Databases, Recordsets, Workspaces, or the QueryDefs collection of a Connection object.

To determine the position that the Microsoft Jet database engine uses for Field objects in the Fields collection of a QueryDef, Recordset, or TableDef object, use the OrdinalPosition property of each Field object. Changing the OrdinalPosition property of a Field object may not change the order of the Field objects in the collection until you use the Refresh method.

Use the Refresh method in multiuser environments in which other users may change the database. You may also need to use it on any collections that are indirectly affected by changes to the database. For example, if you change a Users collection, you may need to refresh a Groups collection before using the Groups collection.

A collection is filled with objects the first time it's referred to and won't automatically reflect subsequent changes other users make. If it's likely that another user has changed a collection, use the Refresh method on the collection immediately before carrying out any task in your application that assumes the presence or absence of a particular object in the collection. This will ensure that the collection is as up-to-date as possible. On the other hand, using Refresh can unnecessarily slow performance.

See Also

Append method, Close method, Delete method, OrdinalPosition property, Requery method.

Example

This example uses the Refresh method to update the Fields collection of the Categories table based on changes to the OrdinalPosition data. The order of the Fields in the collection changes only after the Refresh method is used.

Sub RefreshX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim aintPosition() As Integer
    Dim astrFieldName() As String
    Dim intTemp As Integer
    Dim fldLoop As Field

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind.TableDefs("Categories")

    With tdfEmployees
        ' Display original OrdinalPosition data and store it
        ' in an array.
        Debug.Print _
            "Original OrdinalPosition data in TableDef."
        ReDim aintPosition(0 To .Fields.Count - 1) As Integer
        ReDim astrFieldName(0 To .Fields.Count - 1) As String
        For intTemp = 0 To .Fields.Count - 1
            aintPosition(intTemp) = _
                .Fields(intTemp).OrdinalPosition
            astrFieldName(intTemp) = .Fields(intTemp).Name
            Debug.Print , aintPosition(intTemp), _
                astrFieldName(intTemp)
        Next intTemp
        ' Change OrdinalPosition data.
        For Each fldLoop In .Fields
            fldLoop.OrdinalPosition = _
                100 - fldLoop.OrdinalPosition
        Next fldLoop
        Set fldLoop = Nothing

        ' Print new data.
        Debug.Print "New OrdinalPosition data before Refresh."
        For Each fldLoop In .Fields
            Debug.Print , fldLoop.OrdinalPosition, fldLoop.Name
        Next fldLoop

        .Fields.Refresh

        ' Print new data, showing how the field order has been
        ' changed.
        Debug.Print "New OrdinalPosition data after Refresh."
        For Each fldLoop In .Fields
            Debug.Print , fldLoop.OrdinalPosition, fldLoop.Name
        Next fldLoop

        ' Restore original OrdinalPosition data.
        For intTemp = 0 To .Fields.Count - 1
            .Fields(astrFieldName(intTemp)).OrdinalPosition = _
                aintPosition(intTemp)
        Next intTemp
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example refreshes the Indexes collection of a TableDef object. In a database in a multiuser environment, you can use the Refresh method to show changes made by other users to the Index objects in a TableDef object's Indexes collection.

Sub RefreshIndex()
    Dim dbs As Database, tdf As TableDef
    Dim idx As Index, fld As Field
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Employees
    tdf.Indexes.Refresh
    For Each idx In tdf.Indexes
        Debug.Print idx.Name; ":"
        For Each fld In idx.Fields
            Debug.Print "   "; fld.Name
        Next fld
    Next idx
    Set dbs = Nothing
End Sub