A question from user Jeanniem has not been actually answered, and i noticed that it is closed.
I'm having the same problem (I'm using msaccess 2003)
Can't construct a simple SQL on MS-ACESS VB. I'm using ODBC on an AS400.
I'm having the same runtime error as Jeanniem.
For some reason it is not permitting the use of SELECT Clause.
However it is accepting this string while i'm experimenting.
SQLStr = "LIB.Table1, LIB.Table2 Where Table1.FLD1 = 1 And Table1.FLD1 = Table2.FLD2"
This query is accepted. I think it is assuming that it is in (Select *) default. Notice that i put the join in the where clause (a workaround 'And Table1.FLD1 = Table2.FLD2')
@Jeanniem --> could you try this and give me any feedback? thanks
Set Cnxn = New ADODB.Connection
Set rst1 = New ADODB.Recordset
strCnxn = "Provider='IBMDA400';Data Source='myDSN';User d='';Password='';"
SQLStr = "Select * From LIB.Table1 Inner join LIB.Table2 On Table1.FLD1 = Table2.FLD2 Where FLD1 = 1"
rst1.Open SQLStr, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable