Shape command for MSHFlexGrid

[prachi001]

Hello,

I am using MSHFlexGrid and following is the code:

Dim strCn As String

strCn = "connection string"

Dim strSh As String

strSh = "SHAPE {SELECT * FROM `Customer`} AS Customers APPEND ({SELECT * FROM `Order`} AS Orders RELATE Cust_ID TO Cust_ID) AS Orders"

With Adodc1
.ConnectionString = strCn
.RecordSource = strSh
End With

Set MSHFlexGrid1.DataSource = Adodc1

But when I run code it gives me following error:

Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

What can be done????

Regards,

Prachi

[DataMiser]

"connection string" is not a valid connection string
I do not use the data control nor do I use shape so I am not real sure about your select.

[dglienna]

Here's a sample that uses OLE (which is old)

Private Sub Command1_Click()
    Dim cnLvConnection As ADODB.Connection
    Set cnLvConnection = New ADODB.Connection
    Dim rsLvRecordset As ADODB.Recordset
    Set rsLvRecordset = New ADODB.Recordset
    With cnLvConnection
        .Provider = "MSDataShape.1"
        .ConnectionString = "Data Source=" & App.Path & "\db1.mdb;" _
                          & "Data Provider=Microsoft.Jet.OLEDB.4.0;"
        .Open
        With rsLvRecordset
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        Set rsLvRecordset = .Execute("SHAPE {SELECT c.CustomerName As Customer, c.CustomerID FROM Customers c ORDER BY c.CustomerName} As Customers" _
                                   & " APPEND ((SHAPE {SELECT oh.OrderNumber As [Order No], oh.CustomerID, oh.OrderHeaderID FROM OrderHeaders oh ORDER BY oh.OrderNumber} As OrderHeaders" _
                                   & "          APPEND ({SELECT od.OrderLine As [Line], od.OrderLineDescription As [Description], od.OrderLineQuantity As Quantity, od.OrderHeaderID FROM OrderDetails od ORDER BY od.OrderLine} As OrderDetails" _
                                   & "          RELATE OrderHeaderID TO OrderHeaderID))" _
                                   & " RELATE CustomerID TO CustomerID)")
    End With
    '   Setup Grid
    Set Me.MSHFlexGrid1.Recordset = rsLvRecordset
    Me.MSHFlexGrid1.ColWidth(1, 0) = 0 ' c.CustomerID
    Me.MSHFlexGrid1.ColWidth(1, 1) = 0 ' oh.CustomerID
    Me.MSHFlexGrid1.ColWidth(2, 1) = 0 ' oh.OrderHeaderID
    Me.MSHFlexGrid1.ColWidth(3, 2) = 0 ' od.OrderHeaderID
    '   Tidy up
    If Not rsLvRecordset Is Nothing Then
        If rsLvRecordset.State <> adStateClosed Then
            rsLvRecordset.Close
        End If
        Set rsLvRecordset = Nothing
    End If
    If Not cnLvConnection Is Nothing Then
        If cnLvConnection.State <> adStateClosed Then
            cnLvConnection.Close
        End If
        Set cnLvConnection = Nothing
    End If
End Sub

Private Sub Form_Load()
    Me.MSHFlexGrid1.FixedCols = 0
End Sub

'
'  Edit the cell, without the update

Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer)
   If KeyAscii = vbKeyReturn Then   'enter key
    'move to next cell.
        With MSHFlexGrid1
            If .Col + 1 <= .Cols - 1 Then
                .Col = .Col + 1
            Else
                If .Row + 1 <= .Rows - 1 Then
                    .Row = .Row + 1
                    .Col = 0
                Else
                    .Row = 1
                    .Col = 0
                End If
            End If
        End With
    ElseIf KeyAscii = vbKeyBack Then   'back space key
        With MSHFlexGrid1
            'back space out the entered characters
            If Len(.Text) Then
                .Text = Left(.Text, Len(.Text) - 1)
            End If
        End With
    Else
        With MSHFlexGrid1
            .Text = .Text & Chr(KeyAscii)
        End With
    End If
End Sub

[prachi001]

Thank you so much! it worked