sql - simbolo - Error de sintaxis(operador faltante) en la expresión de consulta

generador de consultas arcgis

Question

Tengo la siguiente declaración SQL que funciona bien en SQL Server pero en Access me da un error

''ERM_HAZARD.[HAZARDID] = ERM_XREF_HAZARD_CAT.[HAZARDID] 
INNER JOIN ERM_HAZARDSTATUS 
ON ERM_HAZARD.HAZARDSTATUSID = ERM_HAZARDSTATUS.HAZARDSTATUSI''

 SELECT erm_hazard.hazardid    AS PKTrans,
       erm_hazard.extref      AS No,
       erm_cat.name           AS CaseType,
       erm_hazard.created     AS CaseDate,
       erm_hazard.name        AS Title,
       erm_hazard.description AS DescriptionTrans,
       erm_organisation.name  AS ResponsibleUnit,
       erm_hazard.reviewdate  AS CaseDeadline,
       erm_hazardstatus.name  AS Status,
       erm_organisation.name  AS UnitInCharge,
       erm_personel.name      AS PersonInCharge
FROM   (erm_personel
        INNER JOIN (erm_organisation
                    INNER JOIN erm_hazard
                            ON erm_organisation.[organisationid] =
                               erm_hazard.[organisationid])
                ON erm_personel.[personelid] = erm_hazard.[personelid])
       INNER JOIN (erm_cat
                   INNER JOIN erm_xref_hazard_cat
                           ON erm_cat.[catid] = erm_xref_hazard_cat.[catid])
               ON erm_hazard.[hazardid] = erm_xref_hazard_cat.[hazardid]
       INNER JOIN erm_hazardstatus
               ON erm_hazard.hazardstatusid = erm_hazardstatus.hazardstatusid
WHERE  (( ( erm_xref_hazard_cat.periodid ) = (SELECT Max(
                                             erm_xref_hazard_cat.periodid)
                                              FROM   erm_xref_hazard_cat) ))
ORDER  BY erm_xref_hazard_cat.periodid DESC,
          erm_hazard.hazardid,
          erm_cat.name  

¿Es esto un problema de tamaño de SQL de acceso? ¿Algunas ideas?

Answer 1

Para agregar a esto Si está creando una Consulta PassThrough, necesita identificar la cadena de conexiones antes de declarar el SQL.

sub test()
Dim dbs As Database
Dim qdf As DAO.QueryDef
Dim rstSQL As DAO.Recordset
Dim strSQL As String

On Error GoTo errTrap

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("")

qdf.Connect = ConStringMailing
qdf.ReturnsRecords = True

qdf.SQL = "Select * FROM TABLE"
Set rstSQL = qdf.OpenRecordset

errTrap:
end sub

Answer 2

MS Access es muy particular con los paréntesis en las consultas. Cuando tiene combinaciones de tablas múltiples, debe haber un par de paréntesis alrededor de cada combinación:

SELECT ERM_HAZARD.HAZARDID AS PKTrans, 
    ERM_HAZARD.EXTREF AS No, 
    ERM_CAT.NAME AS CaseType, 
    ERM_HAZARD.CREATED as CaseDate, 
    ERM_HAZARD.NAME AS Title, 
    ERM_HAZARD.DESCRIPTION AS DescriptionTrans,  
    ERM_ORGANISATION.NAME AS ResponsibleUnit, 
    ERM_HAZARD.REVIEWDATE AS CaseDeadline, 
    ERM_HAZARDSTATUS.NAME as Status, 
    ERM_ORGANISATION.NAME AS UnitInCharge, 
    ERM_PERSONEL.NAME AS PersonInCharge 
FROM ((((ERM_PERSONEL 
INNER JOIN ERM_HAZARD 
    ON ERM_PERSONEL.[PERSONELID] = ERM_HAZARD.[PERSONELID])
INNER JOIN ERM_ORGANISATION 
    ON ERM_ORGANISATION.[ORGANISATIONID] = ERM_HAZARD.[ORGANISATIONID])
INNER JOIN ERM_XREF_HAZARD_CAT 
    ON ERM_HAZARD.[HAZARDID] = ERM_XREF_HAZARD_CAT.[HAZARDID])
INNER JOIN ERM_CAT 
    ON ERM_CAT.[CATID] = ERM_XREF_HAZARD_CAT.[CATID])
INNER JOIN ERM_HAZARDSTATUS 
    ON ERM_HAZARD.HAZARDSTATUSID = ERM_HAZARDSTATUS.HAZARDSTATUSID
WHERE ERM_XREF_HAZARD_CAT.PERIODID= (SELECT MAX(ERM_XREF_HAZARD_CAT.PERIODID) 
                                     FROM ERM_XREF_HAZARD_CAT) 
ORDER BY ERM_XREF_HAZARD_CAT.PERIODID DESC,  ERM_HAZARD.HAZARDID, ERM_CAT.NAME

Answer 3

''ERM_HAZARD. [HAZARDID] = ERM_XREF_HAZARD_CAT. [HAZARDID] INNER UNIRSE A ERM_HAZARDSTATUS EN ERM_HAZARD.HAZARDSTATUSID = ERM_HAZARDSTATUS.HAZARDSTATUSI''

Falta una "D" al final de HAZARDSTATUSID