Execute an insert query include date into access by python

Question

I am trying to execute a query to insert two values into a simple table in an access database. But I got this error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502)(SQLExecDirectW)')

How should I write the date format? My code is like below (The code is working with only integers to insert so I think my date format is no correct):

import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=C:\Users\behdad\Documents\Database1.accdb;')
cursor = conn.cursor()
cursor.execute("insert into Table1 (date, number) VALUES( '2021-02-05 00:00:00', 24)")

The sample table is here: date format in access

Try using # instead ' for delimiters. Date is a reserved word. Advise not to use reserved words as field names. If still have an issue, try [date]. — June7
– June7, Commented Feb 4, 2021 at 11:13

Gord Thompson · Accepted Answer · 2021-02-04 13:35:06Z

1

date and number are both reserved words in Access SQL, so if you have columns with those names you will need to enclose the names in square brackets:

cursor.execute("insert into Table1 ([date], [number]) VALUES( '2021-02-05 00:00:00', 24)")

answered Feb 4, 2021 at 13:35

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute an insert query include date into access by python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related