INFO: Using Resync in ADO with Joined Recordsets (250548)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • ActiveX Data Objects (ADO) 2.7
This article was previously published under Q250548

SUMMARY

Updateable client-side ADO recordsets can be resynchronized by calling the Resync method. This method is different from the Requery method. While the Requery method re-submits the original query, the Resync method generates separate queries to re-fetch the current data for each row in the Recordset.

If you're using a recordset that is based on a join and you update the value of the field that defines the join, you might not see the behavior you'd expect when you resynchronize the recordset. This article explains that scenario in depth as well as how to better handle the scenario.

MORE INFORMATION

In this example, our recordset retrieves orders and employees from the sample Northwind database. In this database, each order is associated with an employee. The code in the example changes the EmployeeID field for a given order in the Orders table to associate that order with a different employee. The goal is to be able to see the desired employee information shortly after performing the update.

The code below retrieves some orders and employee information for those orders. It then changes the EmployeeID field for the first order. Notice that after the update and after resynchronizing the order that the EmployeeID value in the employees half of the join still contains the old information. The desired employee information appears only after calling Requery. The changes to the database are wrapped in a transaction, which you roll back at the end of the code in order to undo those changes. You need to modify the connection string in order to connect to a copy of the Microsoft Access or Microsoft SQL Server Northwind database.

Note You must adjust the Connect String to point to a valid server and must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. 
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _
          "Initial Catalog=Northwind;User ID=<User ID>;Password=<Strong Password>;"
Set cn = New ADODB.Connection
cn.Open strConn

strSQL = "SELECT O.OrderID, O.CustomerID, O.OrderDate, " & _
         "O.EmployeeID AS OrdersEmployeeID, O.OrderDate, " & _
         "E.EmployeeID AS EmployeesEmployeeID, E.FirstName, " & _
         "E.LastName FROM Orders O, Employees E " & _
         "WHERE O.EmployeeID = E.EmployeeID " & _
         "AND O.OrderID < 10270 ORDER BY O.OrderID"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText

cn.BeginTrans

MsgBox "Initially:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID

rs!OrdersEmployeeID = rs!OrdersEmployeeID + 1
rs.Update
MsgBox "After Update:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID

rs.Resync adAffectCurrent
MsgBox "After Resync:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID

rs.Requery
MsgBox "After Requery:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID

rs.Close

cn.RollbackTrans
cn.Close
The reason this behavior occurs is due to how Resync works. The ADO cursor engine does not re-execute the query per row. Instead, it re-fetches data from the tables according to key values from those tables. Because the data comes from two separate tables, the ADO cursor engine uses two separate queries to retrieve this data: 
SELECT OrderID, CustomerID, EmployeeID, OrderDate
       FROM Orders WHERE OrderID = <Current OrderID>
-and- 
SELECT EmployeeID, FirstName, LastName
       FROM Employees WHERE EmployeeID = <Current EmployeeID>
When the ADO cursor engine resynchronizes the employee information for your modified row, it uses the current EmployeeID that is marked as belonging to the Employees table. This is why you see the same employee information, even though you've changed the EmployeeID field that corresponds to the orders table.

There are two dynamic properties available in client-side Recordsets that you can use to better handle this scenario. The first of these properties is called Resync Command and you can use it to specify a query for the ADO cursor engine to use to resynchronize each record. In this case, you want to retrieve employee and order information based solely on the OrderID field using the following query: 
SELECT O.OrderID, O.CustomerID, O.EmployeeID AS OrdersEmployeeID,
       O.OrderDate, E.EmployeeID AS EmployeesEmployeeID, E.FirstName,
       E.LastName FROM Orders O, Employees E
       WHERE O.EmployeeID = E.EmployeeID
             AND O.OrderID = <Current OrderID>
The ADO cursor engine expects a parameter or a set of parameters in the query stored in the dynamic Resync Command property of the Recordset. This parameter or set of parameters corresponds to the primary key field(s) in the unique table in the join. In this case, your unique table is the Orders table. Use the dynamic Unique Table property on the Recordset to specify the unique table.

The code below uses the dynamic Resync Command and Unique Table properties to re-fetch the desired employee information when you call the Resync method:
Note You must adjust the Connect String to point to a valid server and must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. 
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String, strSQL As String

strConn = "Provider=SQLOLEDB;Data Source=MyServer;" & _
          "Initial Catalog=Northwind;User ID=<User ID>;Password=<Strong Password>;"
Set cn = New ADODB.Connection
cn.Open strConn
    
strSQL = "SELECT O.OrderID, O.CustomerID, O.OrderDate, " & _
         "O.EmployeeID AS OrdersEmployeeID, O.OrderDate, " & _
         "E.EmployeeID AS EmployeesEmployeeID, E.FirstName, " & _
         "E.LastName FROM Orders O, Employees E " & _
         "WHERE O.EmployeeID = E.EmployeeID AND O.OrderID < 10270 " & _
         "ORDER BY O.OrderID"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
    
strSQL = "SELECT O.OrderID, O.CustomerID, O.OrderDate, " & _
         "O.EmployeeID AS OrdersEmployeeID, O.OrderDate, " & _
         "E.EmployeeID AS EmployeesEmployeeID, E.FirstName, " & _
         "E.LastName FROM Orders O, Employees E " & _
         "WHERE O.EmployeeID = E.EmployeeID AND O.OrderID = ? "
rs.Properties("Resync Command") = strSQL
rs.Properties("Unique Table") = "Orders"
    
cn.BeginTrans
    
MsgBox "Initially:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID
   
rs!OrdersEmployeeID = rs!OrdersEmployeeID + 1
rs.Update
MsgBox "After Update:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID
  
rs.Resync adAffectCurrent
MsgBox "After Resync:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID
    
rs.Requery
MsgBox "After Requery:" & vbCrLf & _
       "Orders.EmployeeID = " & rs!OrdersEmployeeID & vbCrLf & _
       "Employees.EmployeeID = " & rs!EmployeesEmployeeID
    
rs.Close
    
cn.RollbackTrans
cn.Close

REFERENCES

For more information on the inner workings of the ADO cursor engine, please view the following Web address for Programming ADO - Microsoft Press - ISBN: 0735607648:

http://mspress.microsoft.com/books/3445.htm

Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbDatabase kbinfo KB250548
©2003 Microsoft Corporation. All rights reserved.