Trying to connect to MS Access database through ODBC

Question

Hi,

I am using PowerBI with an ODBC data connection.

When I run my SQL

Select
    T1.ASSR_FEE,
    T1.CAU_DT,
    T1.Incident_Date,
    T1.Inv_amount,
    T1.Rep_Cost,
    T2.Co_Name + " " + T2.Surname as DriverName,
    T3.Co_Name + " " + T3.Surname as InspectorName
from
    cl_details as T1
    Left Join tb_pers as T2 on T1.pers_no = T2.ref_id
    left join tb_pers as T3 on T1.insp_id = T3.ref_id

I get the error message........

Details: "ODBC: ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'T1.pers_no = T2.ref_id
    left join tb_pers as T3 on T1.insp_id = T3.ref_i'."

I have check the syntax online and it seem to be correct... any help appreciated.

Answer 1

How is the SQL statement executed? Code? Are you using a linked table?

BTW, if you are asking a question please change the type of post from "discussion" to "question".

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Hi Paul,

It seems to not like using aliases. 

If I run the code with the below it works.

Select
cl_details.ASSR_FEE,
cl_details.CAU_DT,
cl_details.Incident_Date,
cl_details.Inv_amount,
cl_details.Rep_Cost,
tb_pers.Co_Name as DriverFirstName,
tb_pers.Surname as DriverSurname
from
cl_details
Left Join tb_pers on cl_details.pers_no = tb_pers.ref_id

Yes CL * are linked.

Answer 3

Hi Paul,

It seems to not like using aliases. 

If I run the code with the below it works.

Select
cl_details.ASSR_FEE,
cl_details.CAU_DT,
cl_details.Incident_Date,
cl_details.Inv_amount,
cl_details.Rep_Cost,
tb_pers.Co_Name as DriverFirstName,
tb_pers.Surname as DriverSurname
from
cl_details
Left Join tb_pers on cl_details.pers_no = tb_pers.ref_id

Yes CL * are linked.

To make sure it's the alias, which I still doubt, have you tried adding your third table and it still worked without the alias?

Answer 4

Hello Jak82,

As far as I know, in access, you could only add one join statement directly. For using multiple join statements, you need add some parentheses.

You could try to adjust you query string like

Select
    T1.ASSR_FEE,
    T1.CAU_DT,
    T1.Incident_Date,
    T1.Inv_amount,
    T1.Rep_Cost,
    T2.Co_Name + " " + T2.Surname as DriverName,
    T3.Co_Name + " " + T3.Surname as InspectorName
from
    
    (cl_details as T1
    Left Join tb_pers as T2 on T1.pers_no = T2.ref_id)
    left join tb_pers as T3 on T1.insp_id = T3.ref_id

You could refer to below thread for more information.

https://stackoverflow.com/questions/4779174/left-join-or-select-from-multiple-table-using-comma

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].