Sql command not properly ended

I am unable to see why this error is happening - when tested in Query tool it works great.  what is wrong with the syntax?

K
Public Function FTCSConnection()
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim rstOra As ADODB.Recordset, rs As ADODB.Recordset
    Dim adoRS As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim ctl As Control
    Dim j, I As Long
    Dim rsField, tblField As String
    Dim rsValue, tblValue As String
    Dim varNM As Variant  ' Nomenclature_Modifier
    Dim varLSD As Variant ' Last Service Date
    Dim varRNG As Variant ' Range
    Dim varSDD As Variant ' Service Due Date Cmt
    Dim varEQL As Variant ' Equipment Location
    Dim vtext As String
    Dim varPAmt As Variant ' PURCHASE_AMT
    Dim varPDate As Variant ' PURCHASE_DATE
    Dim rsAdd As New ADODB.Recordset
   
   On Error GoTo FTCSConnection_Error
 
    DoCmd.SetWarnings False
 
    Set cn = CurrentProject.Connection
 
    sConn = _
        "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
        "(Host=hOST1)(Port=1521)))(CONNECT_DATA=(SID=SERVER1)));" & _
        "User Id=******;Password=*****;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    Set adoRS = New ADODB.Recordset
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column SERVICE_DUE_DATE_CMT varchar(14)"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column LAST_SERVICE_DATE varchar(14)"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column PURCHASE_DATE varchar(14)"
         CurrentProject.Connection.Execute STRSQL
    CurrentDb.Execute ("Delete * from TL_FTEM_TEMP")
    CurrentDb.Execute ("Delete * from TL_FTEM")
'    Set rs = New ADODB.Recordset
'    strsql = "Select * from TL_FTEM_TEMP"
'    rs.Open strsql, cn, adOpenDynamic, adLockOptimistic
 
    STRSQL = "SELECT DISTINCT A.FTEM_ID AS FirstofEquipment_ID, A.EQPT_NAME AS NOMENCLATURE," & _
                " A.NOMEN_MODIFIER_NAME AS Nomenclature_Modifier, A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE," & _
                " Count(*) AS CountOfEQUIPMENT_ID, A.SERVICE_DUE_DATE_CMT, A.LAST_SERVICE_DATE," & _
                " A.MFR_NAME AS Manufacturer, A.MFR_NO AS Model, A.PART_VENDOR_SERIAL_NO AS VendorPart," & _
                " A.RANGE, EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
            " FROM FTVI001_FTEM_VI_VW AS A INNER JOIN FTVI001_EQUIPMENT_MANAGEMENT AS EM ON A.FTEM_ID = EM.FTEM_ID" & _
            " GROUP BY A.FTEM_ID, A.EQPT_NAME, A.NOMEN_MODIFIER_NAME, A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE," & _
                " A.SERVICE_DUE_DATE_CMT, A.LAST_SERVICE_DATE, A.MFR_NAME, A.MFR_NO, A.PART_VENDOR_SERIAL_NO," & _
                " A.RANGE, EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
            " ORDER BY A.FTEM_ID"
 
 
    Set adoRS = New ADODB.Recordset
    adoRS.Open STRSQL, adoConn, adOpenDynamic, adLockReadOnly

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please remove DISTINCT, you already have GROUP BY
0
Karen SchaeferBI ANALYSTAuthor Commented:
did that - still get the error.
0
Patrick MatthewsCommented:
Silly question: have you tried ending the SQL statement with a semicolon?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmm.... apart from that, if you run that query against ms access, you are missing some ( ) around the joins and join conditions ...
0
Karen SchaeferBI ANALYSTAuthor Commented:
changed the name of the From from FTVI001_FTEM_VI_VW to FTVI001.FTEM_VI_VW.

still get the error.
0
Karen SchaeferBI ANALYSTAuthor Commented:
Add the semi-colon - still get error
0
Karen SchaeferBI ANALYSTAuthor Commented:
turns out it was the AS name of the tables.

I was able to find the solution elsewhere.

Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.