Search code examples
pythonms-accessodbcpypyodbc

Error in MS Access SELECT statement when number symbol (#) present

When running this minimal code:

import pypyodbc

conn = pypyodbc.connect(r'Driver={{Microsoft Access Driver (*.mdb, *.accdb)}}; Dbq=C:\temp\example.accdb;'
cur = conn.cursor()

cur.execute('SELECT [Pass#] FROM [Companies]')

I get the following error:

pypyodbc.DatabaseError: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.')

The cause of the error appears to be the '#' character, which is a special wildcard character for MS Access. However, I can't figure out any way to escape it. Similar errors suggest the the square brackets ([]) are the way to escape, but it doesn't seem to work.

I have tried these variations like these with no success:

cur.execute('SELECT [Pass[#]] FROM [Companies]')

cur.execute('SELECT Pass[#] FROM [Companies]')

cur.execute('SELECT [Pass\\#] FROM [Companies]')

cur.execute('SELECT Pass# FROM [Companies]')

I should also mention the Access DB is not controlled by me or my company, so I am unable to rename the column.

Solution

  • Thanks to Gord Thompson for pointing me in the right direction. Long story short, the schema I was working against was old, and the field [Pass#] had been renamed to [PassID].

    As it turns out however, the behavior was still confusing for other fields in my DB. For anyone who finds this answer in the future, it would appear that the Access ODBC driver invoked this way will give the error Too few parameters. Expected N. whenever you have wrong column names, instead of a more helpful Column name not recognized or similar. # characters are allowed in the column names, as long as the name is square-bracketed.