Refreshing DAO Collections

In a multiuser setting, users may be modifying collections by adding new tables or queries to the database, or by deleting existing objects. In this case, the Data Access Objects (DAO) library doesn’t automatically keep track of changes to collections.

For example, the following code displays all of the TableDef objects in the current database:

Sub ShowAllTables(dbsTest As Database)

	Dim tdfTemp As TableDef

	For Each tdfTemp In dbsTest.TableDefs
		Debug.Print tdfTemp.Name
	Next tdfTemp
End Sub

When this code runs, it takes a snapshot of the TableDefs collection as it exists at that time. It iterates through each TableDef object and displays its name. Assume that you have to run this code twice. The first time you run it, 10 table names are displayed. But before you can run it again, another user on the network, who also has the database open, adds a new table. Now you run the code again. It still displays 10 table names. This is because DAO doesn’t automatically know about changes made to the collection by other users. To solve this problem, use the Refresh method to force DAO to update the collection with the most recent changes.

To change the previous code so it always displays the most current list of TableDef objects, add the Refresh method, as follows:

Sub BetterShowAllTables(dbsTest As Database)

	Dim tdfTemp As TableDef

	dbsTest.TableDefs.Refresh
	For Each tdfTemp In dbsTest.TableDefs
		Debug.Print tdfTemp.Name
	Next tdfTemp
End Sub

Note   Use the Refresh method only if necessary. If a collection contains a large number of objects, refreshing the collection may take a long time.