Microsoft Office 2000/Visual Basic Programmer's Guide

Requerying Data

If your multiuser solution presents data to the user in a visual form, such as in a window or form, you may want to update the user's view with the most current data. Although this functionality is automatically available to solutions that use the Access objects and user interface, when working with data while using VBA, you must explicitly requery data to get the most current view of other users' changes.

When you use the Requery method with DAO, you must first determine if the Recordset object supports the Requery method by checking the value of the Restartable property of the Recordset object. If the value is True, you can refresh the Recordset object's contents by using the Requery method. If the Recordset object doesn't support the Requery method, you must open the Recordset object again with the Open method.

When you use the Requery method with ADO, you don't have to check the Recordset object to see if it is restartable. The Requery method in ADO handles this automatically; if the Recordset isn't restartable, Requery will reopen the Recordset instead.

The following code illustrates how to requery a Recordset object by using ADO. The OpenRecordsetForRequery procedure opens a Keyset cursor Recordset object on the Orders table and then requeries it.

Sub OpenRecordsetForRequery()
   Dim rst         As ADODB.Recordset
   Dim strConnect  As String

   Const conFilePath As String = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

   ' Format connection string to open database.
   strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
      & conFilePath

   ' Open Recordset object.
   Set rst = New ADODB.Recordset
   With rst
      .Open "Orders", strConnect, adOpenKeyset, adLockOptimistic, _
         adCmdTable
      ' Requery Recordset object.
      .Requery
   End With

   rst.Close
   Set rst = Nothing
End Sub

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

In general, most Recordset objects are restartable, which means they can be requeried. The exceptions are Recordset objects based on pass-through queries and on crosstab queries that contain variable-length fields. These types of Recordset objects can't be requeried and must be reopened to get the most current state of the data. To determine if a Recordset object is restartable from ADO, check the Quick Restart property in the Properties collection of the Recordset object.