uploading data with dates to MS Access database with pyodbc

Question

I'm trying to insert data with a date to MS Access using the pyodbc library in Python. My table has 3 fields: Date (Date/Time), 1Y (Number), and 2Y (Number). I know the problem is not a connection issue since I'm able to insert successfully without the Date field. For example, this works:

cursor.execute("insert into test(1Y,2Y) values (?,?)",'3','4')

Now including the Date, I've tried:

cursor.execute("insert into test(Date,1Y,2Y) values (?,?,?)",'2010-01-01','3','4')
cursor.execute("insert into test(Date,1Y,2Y) values (?,?,?)",date(2010,1,1),'3','4')
cursor.execute("insert into test(Date,1Y,2Y) values (?,?,?)",'1/1/2010','3','4')

For the above examples with Date, I receive the following error: ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)')

I'm using Python 2.7, MS Access 2013, pyodbc 2.7. Thanks.

`Date` is a [reserved word](https://support.microsoft.com/en-us/kb/286335), you need to escape it to use it as column name. — mata, Jan 21 '16 at 16:25
@mata that was it, all of the three versions above work once Date is renamed. Thanks. — tpoh, Jan 21 '16 at 17:11

score 3 · Answer 1 · answered Jan 21 '16 at 17:26

3

To use a reserved word like Date as a column or table name in Access SQL it must be enclosed in square brackets, e.g.,

INSERT INTO TableName ([Date]) VALUES ...

answered Jan 21 '16 at 17:26

Gord Thompson

116,920
32
215
418

score 0 · Answer 2 · answered Jan 21 '16 at 16:24

0

This may not be applicable for Python if it handles qualifying fields for you, however, in some other languages, to insert Date values in Access tables you need to qualify them with # characters, i.e. #01/21/2016#.

answered Jan 21 '16 at 16:24

Aleyn

1
1

uploading data with dates to MS Access database with pyodbc

2 Answers2