VB6 - Runtime error -217217900

watman

Runtime error -217217900 (08004e14)
Data shape command text contains a syntax error at or near position 232 in the command. The text near the error is " RELATE Trainee ID TO Trainee ID) As

I keep getting this error when i try load my main data form in a current program i am working on and can seem to find the source of the issue..

Basically i am trying to show details from two different tables where

Trainee ID - x
Trainee Name - y (Shown in Text Boxes)
Trainee Position - z

Course ID Trainee ID Course Name TrainerID Date
xxy x crystal reports q 4/2/04 (Shown in Grid )
xxx x Visual C++ q 31/1/04

watman

Here is the code i am using during the form load to get the details -

Private Sub Form_Load()
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\TrainingDB\TrainingDB.mdb;"

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "SHAPE {select [Trainee ID],[Trainee Name],Position from [trainee details] Order by [Trainee ID]} AS ParentCMD APPEND ({select [Trainee ID],[Session ID],Date from [trainee record] Order by [Trainee ID] } AS ChildCMD RELATE Trainee ID TO Trainee ID) AS ChildCMD", db, adOpenStatic, adLockOptimistic

Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtFields
Set oText.DataSource = adoPrimaryRS
Next

Set grdDataGrid.DataSource = adoPrimaryRS("ChildCMD").UnderlyingValue

mbDataChanged = False
End Sub

vorbis

watman you seem to have dropped the quare brackets [] for the relate bit of the query. They might be needed, e.g.
RELATE [Trainee ID] to [Trainee ID]

watman

Bang Head off Table !!
Thanks Vorbis - i ll try it with the brackets later