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