Refreshing DAO Collections

In a single-user system, Data Access Objects (DAO) always keep track of changes to collections. As you add objects to collections and delete objects from collections, Microsoft Jet knows about these changes. However, in a multiuser setting, other users may be modifying collections by adding new tables or queries to the database, or deleting existing objects. In this case, DAO doesn’t automatically keep track of changes to collections.

As an example, consider the following code, which displays all the TableDef objects in the current database:

Sub ShowAllTables(dbs As Database)
	Dim tdf As TableDef
	
	For Each tdf In dbs.TableDefs
		Debug.Print tdf.Name
	Next tdf
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 you have to run this code twice. The first time you run it, ten 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 ten table names. This is because DAO doesn’t automatically know about changes made to the collections by other users. To solve this problem, use the Refresh method. The Refresh method forces Microsoft Jet to re-inventory the database and update the collections with the most recent changes.

See Also For more information about DAO, see Chapter 2, “Introducing Data Access Objects.”

To change the previous code to always display the most current list of TableDef objects, add a Refresh method, as follows:

Sub BetterShowAllTables(dbs As Database)
	Dim tdf As TableDef
	 
	dbs.TableDefs.Refresh
	For Each tdf In dbs.TableDefs
		Debug.Print tdf.Name
	Next tdf
End Sub