asked on

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