[RESOLVED] [VB6] hierarchical recordset

hi im creating a hierarchical recordset that will be pass as data report datasource but im receiving the error:


ACCESS VIOLATION or INVALID SYNTAX

Code:

---

Static rs As New ADODB.Recordset
rs.Open "SHAPE {select pono from tblpurchaseorder} " & _
        "APPEND (select description from tblPurchaseOrderItem) AS orderDetails RELATE tblpurchaseorder.recno TO tblpurchaseorderitem.recno", myConnection, adOpenKeyset, adLockReadOnly

Set rptAllSupplierPO.DataSource = rs

rptAllSupplierPO.show

---

btw im using SQL Authentication for the connection object.

Did you specify the MSDataShape provider in the connection string? For SQL Server it should be

myConnection.Open "provider=msdatashape; data provider=sqloledb;data source=your server ;initial catalog=your database;integrated security=sspi"

There is a syntax error in the Shape command. The Related fields must be in both Select statements and each statement must be within curly braces {}

rs.Open "SHAPE {select recno,pono from tblpurchaseorder} " & _
"APPEND ({select recno,description from tblPurchaseOrderItem} RELATE recno TO recno) As OrderDetails", myConnection, adOpenKeyset, adLockReadOnly

perfect!