CODEWITHSUNDEEP
ms-accesspyodbc
TYG
TYG

Reputation: 29

Adding to an MS Access database using pyodbc

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?

Upvotes: 1

Views: 89

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

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.

Upvotes: 1

Related Questions