I'm using the mx.ODBC.Windows package in order to read/write a MSAccess
database. Everything works fine apart from the DATE format handling. I'm
using the default "datetimeformat" (i.e. DATETIME_DATETIMEFORMAT) as
suggested. The date columns in the MSAccess database have "Short Date"
format.
When I read a DATE item everything works fine, like:
conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
data = cursor.fetchall()
print data [(..., <DateTime object for '2003-07-31 00:00:00.00 at address>, ...)]
But when I try to update the table I get a ProgrammingError, like:
date = mx.ODBC.Windows.Date(2003, 07, 31)
conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("INSERT INTO table (..., DATE, ...) VALUES (..., ?,
....)", (..., date, ...))
ProgrammingError: Syntax Error in INSERT INTO statement.
Is it possible to use the mx.DateTime type for MSAccess databases? If so,
how should it be done? If not, how can I insert dates into a MSAccess
database?
Thank you in advance, Fons