Adding to an MS Access database using pyodbc

Question

I am trying to add to a table called Users in an Access database I have created. I am using the following code to do it (which has been copied from here):

import pyodbc
def createAccount():
    conn = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=myPath\User Database.accdb;")
    cursor = conn.cursor()
    cursor.execute("""
            INSERT INTO Users(Username, Password, Chips)
            VALUES("User 5", "Pass 5", 7800)""")
    conn.commit()

but I get this error:

pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
Expected 2. (-3010) (SQLExecDirectW)')

I've seen other posts which say to check spelling of all names used and there isn't anything wrong there. So why isn't this code working?

@GordThompson This worked but would you mind explaining why using " instead of ' causes an error? — TYG
– TYG, Commented Feb 19, 2021 at 17:34

Gord Thompson · Accepted Answer · 2021-02-19 18:33:48Z

1

There are some subtle differences in default behaviour between Access SQL queries executed from within the Access application itself (MSACCESS.EXE) and queries executed from external applications via the Access ODBC driver or the Access OLEDB provider.

From within Microsoft Access itself, both double-quotes (") and single-quotes (') can be used to delimit string literals. This has been true since the earliest versions of Access.

However, the ODBC driver and OLEDB provider at least try to more closely conform to ANSI SQL, so single-quotes (') are used to delimit string literals while double-quotes (") are used to delimit table and column names. Therefore "User 5" will be interpreted as a column name or a parameter name depending on whether such a column actually exists.

edited Feb 19, 2021 at 18:33

answered Feb 19, 2021 at 17:59

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

Adding to an MS Access database using pyodbc

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