Updatable Recordset objects are definitely a RAD concept. Forget about having to fetch data from a Recordset object and into your own data structures, such as Variant arrays, collections, business objects, user-defined types (UDTs), and eXtensible Markup Language (XML) tags! No longer do you have to create and call stored procedures or generate your own action queries! Say goodbye to writing a bunch of code! Decrease the amount of time required to build the database connectivity layer of your application! Why, you can even leverage a plethora of data-bound controls, which can reduce the time you spend developing the user interface layer!
I intentionally worded the previous paragraph to make updatable Recordsets sound as sensational as an infomercial would. (What scares me is that the spelling checker didn't blink at my use of "infomercial.") You might be wondering whether updatable Recordsets will let you scramble an egg while it's still inside its shell. Used properly, updatable Recordsets can save you more time than anything sold in an infomercial. Plus, there's no shipping or handling costs. And just look how this code can cut a tin can in half and still remain sharp enough to slice a tomato!
strSQL = "SELECT * FROM Customers" Set rsCustomers = New ADODB.Recordset rsCustomers.Open strSQL, cnDatabase, adOpenKeyset, _ adLockOptimistic, adCmdText rsCustomers("BalanceDue").Value = rsCustomers("BalanceDue").Value + 50 rsCustomers.Update |
Somewhere between the marketing hype (marketers who say that you can build a multiuser database application in five minutes missed their calling as used car salespeople) and the stubborn and sometimes fanatical developers who'd rather perish than use someone else's code (trust no one…else's code) lies the middle ground where you'll find the truth. Let's take a brief look at updatable server-side and client-side Recordsets and examine their pros and cons.
Chapter 7 covered keyset cursors and dynamic cursors—the types of server-side cursors that support updating. In this section, you'll see how to use the Recordset object to interact with these cursors.
When you modify the Value property of the various Field objects in your Recordset and then call the Recordset's Update method, ADO passes that information to the server-side cursor through the OLE DB provider or ODBC driver. But who updates the database? Basically, whoever manages the cursor handles that job.
Remember that when you work with SQL Server, the OLE DB provider and ODBC driver communicate with the database and allow you to access the data in the cursor. SQL Server actually manages the cursor. SQL Server also updates data in your database as you modify the contents of the cursor.
The Access OLE DB provider and ODBC driver manage the cursor and directly communicate with the database as well as let you interact with the cursor. Therefore, they're also responsible for updating your database based on the changes made to the cursor.
The Microsoft ODBC driver for Oracle exposes and manages the cursor. (Perhaps someday the related OLE DB provider will do this too.) As you change data in the cursor, the ODBC driver interprets these changes and tells the Oracle database server how to modify the underlying data in your database.
What are the pros and cons of using server-side Recordsets to update your database? Let's look at the advantages first:
Now let's examine the disadvantages of updating your database by using server-side Recordsets:
Client-side Recordsets offer a middle ground between action queries and server-side Recordsets. When it comes to modifying the Recordset object, client-side Recordsets behave the same as server-side Recordsets. However, instead of communicating with a cursor that's maintained by the database, the OLE DB provider, or the ODBC driver, ADO retrieves the results of your query and stores that data in its own Cursor Engine.
The data in your Recordset is static, meaning you won't see changes other users make to the data in the database. Once the ADO Cursor Engine has retrieved the results of your query, you can disconnect your Recordset and you won't need to communicate with your database until you're ready to modify its contents or retrieve more data.
The ADO Cursor Engine examines the changes you make to the Recordset and translates those changes into action queries. Based on the number of records modified by those action queries, the Cursor Engine determines whether the action query successfully updated the data in your database. The next few chapters will explore this process in much more depth.
Now let's examine the advantages and disadvantages of using client-side Recordsets to update your database. First, we'll take a look at the advantages:
There's only one significant drawback to using client-side Recordsets to update your database. By allowing the ADO Cursor Engine to maintain the results of your queries and update your database, you still give up some control.