Microsoft Office 2000/Visual Basic Programmer's Guide

Refreshing Collections

Any time you are working with collections it is a good idea to use the Refresh method to refresh the contents of the collection before working with it. For example, if your procedure works with a collection, calls a procedure that adds or deletes objects from the collection, and then returns and works with the collection again, the contents of the collection often won't be current unless you've refreshed it. This problem can be compounded even further in a multiuser setting, because other users may be modifying collections by adding new objects to the database, or deleting existing objects. The following code fragment loops through the Table objects in a Tables collection. By using the Refresh method to force ADO to reinventory the database and update the Tables collection with the most recent changes, it ensures that the contents of the Tables collection are current.

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

' Refresh the collection before proceeding.
cat.Tables.Refresh

For Each tbl In cat.Tables
   If tbl.Type = "TABLE"
      Debug.Print tbl.Name
Next tbl

This code fragment is from the ShowAllTables procedure, which can be found in the MultiuserIssues module of the MultiuserDatabase.mdb sample file, available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.