MSAccess update statement with string containing special characters and spaces

Question

I am trying to update rows in a column in MS Access using Python. The string I want to update is a Windows file path which contains special characters and spaces like:

C:\Users\Manish\Desktop\Personal\greencindiaphotos25-10-2016\8.2.Patna_west_00001-14335_W\PA11379.jpg

However, when I run the sql query to update, it throws this error:

pypyodbc.ProgrammingError: (u'42000', u"[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'C:\Users\Manish\Desktop\Personal\greencindiaphotos25-10-2016\8.2.Patna_west_00001-14335_W\PA11379.jpg'.")

It's a simple update query like:

UPDATE Vaishali SET Images={file_path} WHERE ID=112312;

How to update string like this in access?

score 0 · Answer 1 · answered Nov 26 '16 at 13:40

You should be using a parameterized query, e.g.

file_path = r"C:\Users\Manish\Desktop\Personal\greencindiaphotos25-10-2016\8.2.Patna_west_00001-14335_W\PA11379.jpg"
id = 112312
sql = "UPDATE Vaishali SET Images=? WHERE ID=?"
params = (file_path, id)
crsr.execute(sql, params)

MSAccess update statement with string containing special characters and spaces

1 Answers1