Chapter 10 -- How the ADO Cursor Engine Updates Your Database

[Previous] [Next]

Chapter 10

Whenever developers ask me why they're getting errors when they use a client-side Recordset to update their data, I ask them if they understand how ADO tries to update their data. I'm sometimes surprised by their reaction to that question. The most common response is, "Why should I care what ADO is doing? I just want my code to work."

Unfortunately, I can't send customers to their rooms, and telling them I'm disappointed in them rarely generates much of a response. The less you know about how ADO updates your database when you use a client-side cursor, the slimmer the chances that your application will work reliably. That statement usually gets a programmer's attention.

Let's look at a quick and dirty example of an update operation:

 strSQL = "SELECT CustomerID, CompanyName, BalanceDue FROM Customers" rsCustomers.CursorLocation = adUseClient rsCustomers.Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText rsCustomers.Find "CustomerID = 7" rsCustomers.Fields("BalanceDue") = rsCustomers.Fields("BalanceDue") + 50 rsCustomers.Update 

You can run this code and successfully add $50 to the balance due for the customer with a CustomerID of 7. But what really happened?

Let's start with a little bit of background. I used to be an Xbase programmer. In Xbase, it's easy to tell that the record you're accessing programmatically is the actual record in the actual table in the actual database file.

Microsoft Access is a little more complex. The Jet engine keeps its own copy of the results of your query. But when you modify a record in your Data Access Objects (DAO) Recordset (or your server-side ADO Recordset), the Jet engine physically locates and modifies the desired record.

The ADO Cursor Engine doesn't work like Jet or like Xbase. Remember that the data in a client-side cursor is inherently disconnected from the database. ADO retrieves the results of the selection query and copies the data into the ADO Cursor Engine. After you make changes to your Recordset, the ADO Cursor Engine translates those changes into an action query and submits that query to your database through the OLE DB provider or ODBC driver.

Referring back to our example, you can see that ADO retrieves records into the Recordset based on the results of the query in the strSQL string. Now let's say that the customer's balance due prior to running this code was $125. When you modify that data, the ADO Cursor Engine builds a new query to update the customer's balance due:

 UPDATE Customers SET BalanceDue = 175 WHERE CustomerID = 7 AND BalanceDue = 125 

If instead of modifying the balance due you deleted or added a customer by using the Recordset's Delete or AddNew method, the respective action queries created by the ADO Cursor Engine would look like this:

 DELETE Customers WHERE CustomerID = 7 

and

 INSERT INTO Customers (CustomerID, CompanyName, BalanceDue) VALUES (7, 'Seventh Heaven', 175) 

Voilà! It's not quite magic, but it's still pretty impressive if you ask me.

Keep in mind that when you write query statements, you have a distinct advantage over ADO: You (should) know the name of the table you're modifying as well as the name of the field or fields that constitute the primary key. But we didn't set any properties on the Field or Recordset object to provide this information to ADO when we modified the Recordset. So how did the ADO Cursor Engine know how to construct this query? Through the use of metadata.