Updates the objects in the specified colletion to reflect the database's current schema.
Syntax
expression .Refresh
expression A variable that represents a Fields object.
Remarks
To determine the position that the Microsoft Access 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.
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