Microsoft KB Archive/250548

= INFO: Using Resync in ADO with Joined Recordsets =

Article ID: 250548

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 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= and 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=;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 =  -and- SELECT EmployeeID, FirstName, LastName FROM Employees WHERE 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 =  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= and 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=;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

