Requerying Data

If your multiuser application 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. While this functionality is automatically available to applications using the Microsoft Access user interface, an application based on Visual Basic or some other language must explicitly requery data to get the most current view of other users’ changes.

To obtain the most current view of data in a Recordset object, you must first determine if the Recordset object supports the Requery method. You can do this 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. This causes Microsoft Jet to repopulate the Recordset object with the most current data.

If the Recordset object doesn’t support the Requery method, you must open the Recordset object again with the OpenRecordset method. The following code illustrates how to generically requery a Recordset object. The OpenRecordsetForRequery procedure opens a dynaset-type Recordset object on the Orders table. It then tries to refresh the Recordset object’s contents by calling the RequeryIfYouCan function. If this fails, it reopens the Recordset object.

Sub OpenRecordsetForRequery(strDbPath As String)
	Dim dbs As Database
	Dim rst As Recordset
	
	' Open database in shared mode.
	Set dbs = OpenDatabase(strDbPath, False)
	' Open dynaset-type recordset.
	Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
  
	' Call RequeryIfYouCan function.
	If RequeryIfYouCan(rst) Then
		Debug.Print "Recordset has been refreshed."
	Else
		' If recordset cannot be requeried, reopen it.
		Set rst = dbs.OpenRecordset _
			("Orders", dbOpenDynaset)
		Debug.Print "Recordset has been reopened."
	End If
  
	rst.Close
	dbs.Close
End Sub

Function RequeryIfYouCan(rst As Recordset) As Boolean
	' Check recordset's Restartable property.
	If rst.Restartable Then
		' Requery recordset.
		rst.Requery
		RequeryIfYouCan = True
	Else
		RequeryIfYouCan = False
	End If
End Function

In general, most Recordset objects are restartable, which means they can be requeried. The exceptions are recordsets based on pass-through queries and on crosstab queries that contains variable-length fields. These types of recordsets cannot be requeried and must be reopened to get the most current state of the data.