PYODBC ProgrammingError: ('42000', "[42000] [Microsoft][Pilote ODBC Microsoft Access]

Question

I try to do an update query using a left join on another mdb.

Into a cursor for the first MDB, I execute this query:

update table as ori 
    left join (select * 
               from param in "E:/Jeter/param_141114.mdb" 
               where zone = '1H005') param 
    on ori.dep_sur = param.dsu_co 
set ori.texture = param.textu where mid(ori.type,4,1) in ('0','7','8')

When I launch this query from Microsoft Access, no problem, the query is applied.

When I launch this query from python 2.7 with pyodbc, here my result translated from french:

ProgrammingError ('42000', "[42000] [Microsoft] [ODBC Microsoft Access Driver] The database engine can not find [E: /Jeter/param_141114.mdb] 'Make sure the name. parameter or alias is valid, he does not understand character or incorrect punctuation and that it is not too long. (-1002) (SQLExecDirectW) ")

Some ideas?

Solution

Apparently the SELECT ... FROM TableName IN "FileName" ... syntax is not available to ODBC queries from external applications. However, I just tried the following variation and it worked for me (Python 2.7 and pyodbc):

sql = """
update tableau as ori 
    left join (select * 
               from [C:/__tmp/test.mdb].param 
               where zone = '1H005') param 
    on ori.dep_sur = param.dsu_co 
set ori.texture = param.textu
"""
crsr = db.execute(sql)
crsr.commit()