syntax error (missing operator) in query expression – VBA and Access

I build a query with this syntax:

SELECT e.codigo AS `Código`,
       e.razao_social AS `Razão Social`,
       e.grupo AS `Grupo`,
       e.tributacao AS `Tributação`,
       e.sistema AS `Sistema`,
       r.nome AS `Responsável`,
       date_format(t.competencia, '%m/%Y') AS `Competência`,
       s.nome AS `Status`,
       c.nome AS `Tipo Conferência`
FROM tarefa AS t
RIGHT JOIN empresa AS e ON t.id_empresa = e.id_empresa
LEFT JOIN responsavel AS r ON t.id_responsavel = r.id_responsavel
LEFT JOIN status AS s ON t.id_status = s.id_status
LEFT JOIN conferencia AS c ON t.id_conferencia = c.id_conferencia
WHERE c.nome = 'Encerramento Contábil'
ORDER BY `Competencia`;

I did a test query in MariaDB, and it worked.

Now, i am use this query in Access with VBA MSExcel, where it has the same structure and relationship between tables, but return error.

message error vba:

Syntax error (missing operator) in expression ‘t.id_empresa = e.id_empresa LEFT JOIN responsavel AS r ON t.id_respons’

This is my code vba MSExcel:

Sub testSql()

'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String

    'Declaring fully qualified name of database. Change it with your database's location and name.
    DBPATH = "C:Usersctb06DocumentsDatabase2.accdb"
    
    'This is the connection provider. Remember this for your interview.
    PRVD = "Microsoft.ace.OLEDB.12.0;"
    
    'This is the connection string that you will require when opening the the connection.
    connString = "Provider=" & PRVD & "Data Source=" & DBPATH
    
    'opening the connection
    conn.Open connString
    
    'the query I want to run on the database.
    query = "SELECT e.codigo AS `Código`, e.razao_social AS `Razão Social`, e.grupo AS `Grupo`, e.tributacao AS `Tributação`, e.sistema AS `Sistema`, r.nome AS `Responsável`, date_format(t.competencia, '%m/%Y') AS `Competência` FROM tarefa AS t RIGHT JOIN empresa AS e ON t.id_empresa = e.id_empresa LEFT JOIN responsavel AS r ON t.id_responsavel = r.id_responsavel;"
        
    'running the query on the open connection. It will get all the data in the rec object.
    rec.Open query, conn
    
    'clearing the content of the cells
    Range("a1").Select
    Cells.ClearContents
    
    If (rec.RecordCount <> 0) Then
        col = 1
        For Each resp In rec.Fields
        
            With Cells(1, col)
                .Value = resp.Name
            End With
            
            col = col + 1
        
        Next resp
        
        Cells(2, 1).CopyFromRecordset rec
    
    End If
    

    rec.Close
    conn.Close
    

End Sub

Where am I missing?

Answer

No two SQL dialects are exactly the same for exact transferability. Simply, MariaDB’s SQL will not perfectly align to MS Access’ SQL similar to running same query from Oracle to Postgres, MySQL to SQL Server, Sybase to DB2… There will need to be some translation.

Specifically:

• DATE_FORMAT is not an available function in MS Access. Instead use FORMAT with appropriate format pattern which does not use %.

• More than one JOIN require parentheses wrapping in MS Access. However, your mix of RIGHT JOIN and LEFT JOIN may require nested joining.

  (Admittedly, this is a frustrating requirement for new Access users to build complex queries with Query Designer and not SQL. I raised this suggested change among others to Access’ SQL dialect.)

• Fortunately, backticks are supported in MS Access though square brackets, [...], are the more popular form to escape identifiers with special characters or keywords.

Consider following adjustment:

SELECT e.codigo AS `Código`,
       e.razao_social AS `Razão Social`,
       e.grupo AS `Grupo`,
       e.tributacao AS `Tributação`,
       e.sistema AS `Sistema`,
       r.nome AS `Responsável`,
       FORMAT(t.competencia, 'mm/yyyy') AS `Competência`,
       s.nome AS `Status`,
       c.nome AS `Tipo Conferência`
FROM ((((empresa AS e 
RIGHT JOIN tarefa AS t ON t.id_empresa = e.id_empresa)
LEFT JOIN responsavel AS r ON t.id_responsavel = r.id_responsavel)
LEFT JOIN status AS s ON t.id_status = s.id_status)
LEFT JOIN conferencia AS c ON t.id_conferencia = c.id_conferencia)
WHERE c.nome = 'Encerramento Contábil'
ORDER BY `Competencia`;