Hi Pete...

Glad to be of help here...

Here is some revised code:

oConnection = create('adodb.connection')
oRecordset= create('adodb.recordset')
With oConnection
.Provider = 'MSDataShape'
.ConnectionString = "Persist Security Info=False;Data Source=E:\MICROSOFT VISUAL STUDIO\VB98\nwind.mdb;Data Provider=MICROSOFT.JET.OLEDB.4.0"
.Open
EndWith

ShapeString = "SHAPE {SELECT * FROM `Customers`} AS Customers "
ShapeString = ShapeString + "APPEND (( SHAPE {SELECT * FROM `Orders`} AS Orders "
ShapeString = ShapeString + "APPEND ({SELECT * FROM `Order Details`} AS OrderDetails "
ShapeString = ShapeString + "RELATE 'OrderID' TO 'OrderID') AS OrderDetails) AS Orders "
ShapeString = ShapeString + "RELATE 'CustomerID' TO 'CustomerID') AS Orders"

oRecordSet.Open(ShapeString,oconnection,3,4)


Do While !orecordset.eof()
?oRecordSet.Fields("companyname").Value
oRecordSet.Fields("companyname").Value = ;
Alltrim(oRecordSet.Fields("companyname").Value)
oOrders = oRecordset.Fields("orders").Value
Do While !oorders.eof()
?oOrders.Fields("orderID").Value
oOrderDetails = oOrders.Fields("OrderDetails").Value
oOrders.MoveNext
Do While !oorderdetails.eof()
?oOrderDetails.Fields("productid").Value
?oOrderDetails.MoveNext
EndDo
EndDo
orecordset.movenext
EndDo

oRecordset.UpdateBatch

Several things. In the book, I renamed my access table - Order Details to OrderDetails. You don't have to do this step. The above code should work with how NWIND.MDB looks out of the box.

The above code uses the JOLT OLE-DB provider - not the OLE-DB Provider for ODBC. I have found the native OLE-DB Providers to be much more reliable.

Also, I threw in a simple update to show that you can indeed - update data in hierachical recordsets.

Here is something else you may find kind of neat. Here is the code:

oConnection = createobject('adodb.connection')
oCommand = CreateObject("adodb.command")
With oConnection
.Provider = 'SQLOLEDB'
.ConnectionString = "Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=(local);Connect Timeout=15"
.Open
EndWith

SQLString = "Select * From Customers"
SQLString = SQLString + Chr(13)
SQLString = SQLString + "Select * From Orders"

With oCommand
.CommandText = SQLString
.CommandType = 1
.ActiveConnection = oConnection
EndWith

oRecordSet = oCommand.Execute

Do while !oRecordSet.Eof()
?oRecordSet.Fields("companyname").Value
oRecordSet.MoveNext
EndDo

oOrders = oRecordset.NextRecordSet
Do while !oorders.Eof()
?oORders.Fields("Orderid").Value
oOrders.MoveNext
EndDo

You can have multiple distinct recordsets housed in one recordset object. Unlike the first example, which is 1 hierachical recordset, this recordset has two recordsets in its boundaries. This is where the NextRecordset Method comes into play.

In this case, I am using SQL Server - and the OLE-DB Provider for SQL Server. I don't think multiple recordsets can be returned using Access.

None of this by the way, will work with VFP data.

Let me know how things go...