Joins

[Previous] [Next]

In a perfect world—or a perfect data access object model—you could update data based on a join that you design however you see fit. You'd have enough control to tell the object model exactly what it means to insert, modify, or delete a record in your Recordset. But as I'm sure you've noticed, this is not a perfect world and ADO is not a perfect data access object model. Although queries that use joins are fine for read-only data, updating a Recordset based on a join query is not as easy as you might think.

Let's take a look at a simple update example. Suppose that you want to retrieve customer and order information into a single recordset by joining information, as in the following query:

 SELECT * FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID 

Obviously if you want to modify order data in this recordset, that change would correspond to a single record in the Orders table. Simple enough. But what if you change the value of fields from both tables? Say that the CompanyName field exists in the Customers table and that the OrderTotal field exists in the Orders table:

 rsOrdersAndCustomers("CompanyName").Value = "Some New Customer Name" rsOrdersAndCustomers("OrderTotal").Value = 1500 

What does this code mean?

ADO understands that this code requires changes to both tables. The Cursor Engine builds separate action queries to submit to each of the tables. If that's what you want to happen, great! Wonderful! So what's the problem? If you're only worried about a single isolated update such as this, there is no problem.

However, what if you want to modify the record to indicate that the order belongs to a different customer? If you modify fields that correspond to fields in the Customers table, the ADO Cursor Engine will generate an action query to modify the record in the Customers table. This can be a problem if you wanted the changes in the record to update only the unique table produced by the join.

As a general rule, no matter what fields you modify in your recordset, the ADO Cursor Engine will attempt to update the tables associated with them.

Insertions

What if you want to enter a new record into this recordset? If you simply want to create a new order, you'll have to set the Value property on only the Field objects that correspond to the Orders table. Once you commit the change to the database, the ADO Cursor Engine will generate an action query to insert your new record into the Orders table. But if you set the Value property on Field objects corresponding to the Customers table, the ADO Cursor Engine will also try to insert a record into the Customers table.

Deletions

Deletions are a little simpler. If you try to delete a record from this recordset, the ADO Cursor Engine will build action queries to delete the order from the Orders table as well as the customer from the Customers table. If that's what you intended to do, you should be just fine.

If you wanted to delete only the record from the unique table (in this case the Orders table), you would need to use another dynamic property on the Recordset object that's exposed by the Cursor Engine: Unique Table.

 strSQL = "SELECT * FROM Customers, Orders " & _ "WHERE Customers.CustomerID = Orders.CustomerID" rsCustomersAndOrders.CursorLocation = adUseClient rsCustomersAndOrders.Open strSQL, cnDatabase, adOpenStatic, _ adLockOptimistic, adCmdText rsCustomersAndOrders.Properties("Unique Table").Value = "Orders" rsCustomersAndOrders.Delete 

By setting the Unique Table dynamic property on the Recordset object to Orders, you're telling the ADO Cursor Engine to delete only the record from the Orders table and ignore the other tables referenced by the query. This dynamic property exists on all client-side Recordset objects but is helpful only to deletions in joins. Currently, ADO ignores the Unique Table dynamic property and the related Unique Schema and Unique Catalog dynamic properties for modifications and insertions with joins.