PYODBC ProgrammingError: ("42000", "[42000] [Microsoft] [Microsoft Access]
I am trying to execute an update request using a left join on a different mdb.
At the 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 run this query from Microsoft Access no problem occurs, the query is applied.
When I run this request from python 2.7 with pyodbc, here's my result translated from French:
ProgrammingError ('42000', "[42000] [Microsoft] [ODBC Microsoft Access Driver] The database engine cannot find [E: /Jeter/param_141114.mdb]" Make sure the name. The parameter or alias is valid, it does not understand the character or incorrect punctuation and that it's not too long. (-1002) (SQLExecDirectW) ")
Some ideas?
source to share
The syntax appears to be SELECT ... FROM TableName IN "FileName" ...
not available for ODBC queries from external applications. However, I just tried the following option 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()
source to share