>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...


John,
thanks for your time and code, The first example runs a treat after modifying the Open method to oConnect.Open('mynwind') as I don't have the Jet driver installed, the second example returns a "General Network Error" - "Check your Server Documentation" this occurs when the connection objects' Open method is called. By the way I'm still using VFP 5.0a ( have ordered 6 ) is this a problem?

Thanks again
Pete Kane