3131 Errore sintassi clausola FROM

Question

Salve,

vorrei provare a leggere dei dati direttamente su SS.

Scrivo il seguente codice:

gSQL = "SELECT FF.COD_FIELD_FORCE, C.TXT_CONTEST_NAME, C.TXT_DESIGNATION," _

& "       FF.COD_BRANCH, FF.COD_DISTRICT, FF.COD_TEAM, US.COD_US, " _

& "       FF.DAT_CONTRACT, FF.STA_FIELD_FORCE, FF.COD_STATUS_REASON," _

& "       FF.IND_EXPERIENCE_IA, B.MNT_AMOUNT, FC.COD_CATEGORY, P.NO_PERIOD" _

& "  FROM DATE D, BALANCE B, FIELD_FORCE FF, US US, " _

& "       CLIENT C, PERIOD P, FINANCIAL_CATEGORY FC" _

& " WHERE B.COD_BUSINESS    = 'P'" _

& "   AND FC.COD_CATEGORY  IN('FYCWR', 'WRBUSTOTAL', 'FYCWANNSEG', 'FYCWANNFXT')" _

& "   AND P.IND_CURRENT     = 'Y'" _

& "   AND FF.ID_US          = US.ID_US" _

& "   AND FF.ID_CLIENT      = C.ID_CLIENT" _

& "   AND B.ID_FIELD_FORCE  = FF.ID_FIELD_FORCE " _

& "   AND B.ID_FIN_CATEGORY = FC.ID_FIN_CATEGORY" _

& "   AND B.ID_DATE         = D.ID_DATE " _

& "   AND D.ID_PERIOD       = P.ID_PERIOD"

Dim qPT As DAO.QueryDef

Dim gStrConn As String

Set qPT = CurrentDb.CreateQueryDef("", gSQL)

---

With qPT

gStrConn = "ODBC;DRIVER=SQL Server;SERVER=mio_SRV;DATABASE=mio_DB;"

.Connect = gStrConn

.ReturnsRecords = True

End With

Set rsRead = qPT.OpenRecordset(dbOpenSnapshot)

Al momento di aprire il rsRead ho l'errore.

Riprovo con debug, copio la stringa SQl su Management Studio di SS e mi escono i dati.

Non capisco.

Answer 1

Ciao Francesco,

Date in Access è una parola riservata, racchiudila tra parentesi quadre.

---

gSQL = "SELECT FF.COD_FIELD_FORCE, C.TXT_CONTEST_NAME, C.TXT_DESIGNATION," _

         & "       FF.COD_BRANCH, FF.COD_DISTRICT, FF.COD_TEAM, US.COD_US, " _

         & "       FF.DAT_CONTRACT, FF.STA_FIELD_FORCE, FF.COD_STATUS_REASON," _

         & "       FF.IND_EXPERIENCE_IA, B.MNT_AMOUNT, FC.COD_CATEGORY, P.NO_PERIOD" _

         & "  FROM [DATE] D, BALANCE B, FIELD_FORCE FF, US US, " _

         & "       CLIENT C, PERIOD P, FINANCIAL_CATEGORY FC" _

         & " WHERE B.COD_BUSINESS    = 'P'" _

         & "   AND FC.COD_CATEGORY  IN('FYCWR', 'WRBUSTOTAL', 'FYCWANNSEG', 'FYCWANNFXT')" _

         & "   AND P.IND_CURRENT     = 'Y'" _

         & "   AND FF.ID_US          = US.ID_US" _

         & "   AND FF.ID_CLIENT      = C.ID_CLIENT" _

         & "   AND B.ID_FIELD_FORCE  = FF.ID_FIELD_FORCE " _

         & "   AND B.ID_FIN_CATEGORY = FC.ID_FIN_CATEGORY" _

         & "   AND B.ID_DATE         = D.ID_DATE " _

         & "   AND D.ID_PERIOD       = P.ID_PERIOD"

    Dim qPT As DAO.QueryDef

    Dim gStrConn As String

    Set qPT = CurrentDb.CreateQueryDef("", gSQL)

---

Ciao

Giorgio Rancati