How to write this query in MS ACCESS 2002 VBA

hei all
I have a query in access which I have to excecute and get the result of this query in a recordset.
I have copied the SQL statement of the sql view of this query and made some changes but it is not working.
Saying that "Syntax is failed "
can any one , who is good in writing SQL queries in access please help me.
I am attaching the query how it looks in design view of Access.


This is the way how it looks in SQL view of access.
__________________________________________________ _________

SELECT Make.id, Make.description, Motor.id, Motor.Art, Motor.Litre, Motor.KW, Motor.description, Vehicletype.Vehicle_Type_id, Vehicletype.description, Vehicletype.year, Getriebe.id, Getriebe.Art, Getriebe.Nummer, Getriebe.description, Antriebe.id, Antriebe.Art, Antriebe.nummer, Antriebe.description, Vehicletype.objid, Vehicletype.Türen, Vehicletype.XL_DCHA_Path, Vehicletype.XL_DCHATA_path, Vehicletype.XL_DCHATATP_Path, Vehicletype.XL_XTHA_Path, Vehicletype.XL_XTTA_Path, Vehicletype.XL_Empty_Path, Vehicletype.XL_Pic_Path, Vehicletype.XL_Graphic_Path
FROM Motor RIGHT JOIN (Make RIGHT JOIN (Getriebe RIGHT JOIN (Antriebe RIGHT JOIN Vehicletype ON Antriebe.objid = Vehicletype.Antriebe_objid) ON Getriebe.objid = Vehicletype.Getriebe_objid) ON Make.objid = Vehicletype.Make_objid) ON Motor.objid = Vehicletype.Motor_objid;

__________________________________________________ ____________

Please help me!..
thanks in advance,
Kiran.

Hello Kiran,

Probably, the select statement is too long to fit in one line. You may need to break it down into several lines by using "_" at the end of each line. Hope it will help.


Since this message section is not allow me to put more than 95 characters per line. That's why I chopped it off so many lines. You can just break this statement into 3 or 4 lines, that is good enough.

'-----------------
Dim MyDB As Database
Dim MySet As Recordset
Dim strSQL As String

strSQL = "SELECT Make.id, Make.description, Motor.id, Motor.Art, Motor.Litre, Motor.KW," & _
" Motor.description, Vehicletype.Vehicle_Type_id, Vehicletype.description," & _
" Vehicletype.year, Getriebe.id, Getriebe.Art, Getriebe.Nummer, Getriebe.description," & _
" Antriebe.id, Antriebe.Art, Antriebe.nummer, Antriebe.description, Vehicletype.objid," & _
" Vehicletype.Türen, Vehicletype.XL_DCHA_Path, Vehicletype.XL_DCHATA_path," & _
" Vehicletype.XL_DCHATATP_Path, Vehicletype.XL_XTHA_Path, Vehicletype.XL_XTTA_Path," & _
" Vehicletype.XL_Empty_Path, Vehicletype.XL_Pic_Path, Vehicletype.XL_Graphic_Path" & _
" FROM Motor RIGHT JOIN (Make RIGHT JOIN (Getriebe RIGHT JOIN" & _
" (Antriebe RIGHT JOIN Vehicletype ON Antriebe.objid = Vehicletype.Antriebe_objid)" & _
" ON Getriebe.objid = Vehicletype.Getriebe_objid) ON Make.objid = Vehicletype.Make_objid)" & _
" ON Motor.objid = Vehicletype.Motor_objid;"

Set MyDB = CurrentDB
Set MySet = MyDB.OpenRecordSet(strSQL)

Do Until MySet.EOF

'Insert your manipulated statement here!

MySet.MoveNext
Loop

'------------------------