Questions That Should Be Asked More Frequently

[Previous] [Next]

Q. How does the StayInSync property on the Recordset object work? I can never make it work the way I want.

A. The StayInSync property controls whether a reference to a child level of a hierarchy will remain synchronized with the current position of its parent level. Say you open a customers and orders hierarchy with the customer data as the parent and obtain a reference to the orders level of the hierarchy. By default, as you navigate through the customer data, the orders Recordset object variable will always contain the orders for the current customer. Suppose you want to reference orders for a customer without the contents of the orders object variable changing when you move to another customer. You can set the StayInSync property on the customers Recordset to False before obtaining a reference to the orders level of the hierarchy, as shown here:

 rsCustomers.Open strHierarchicalQuery, cnDatabase, adOpenStatic, _ adLockReadOnly, adCmdText rsCustomers.StayInSync = False Set rsOrders = rsCustomers.Fields("Orders").Value 

For more information on the Recordset's StayInSync property, see Chapter 4.

Q. I want to base the relationship of a hierarchy on an auto-incrementing field, but I'm having problems when adding new records to the parent and child. What should I do?

A. This is a fairly common question, so I created a sample named Identity In Hierarchy to address it. The sample uses the Orders and Order Details tables in the SQL Server Northwind database. You can view this sample on the companion CD and learn more about it in Appendix B.

Q. When programming in Visual Basic, do I need to use the Value property on the Field object when referencing a child level of a hierarchical Recordset?

A. Yes. The following code makes use of the fact that the default property on the Field object is the Value property. The Value property on the CompanyName Field object will be stored in the strCompanyName string variable:

 Dim strCompanyName As String strCompanyName = rsCustomers.Fields("CompanyName") 

The following code, however, will fail with a type mismatch error:

 Dim rsOrders As ADODB.Recordset Set rsOrders = rsCustomers.Fields("CompanyName") 

Why? Using the Set keyword means you want to reference an object, so this code will actually try to set a Recordset object variable to a Field object. Thus, you need to write your code as shown here:

 Dim rsOrders As ADODB.Recordset Set rsOrders = rsCustomers.Fields("CompanyName").Value