Python Pydobc SQL error - Number of query values and destination fields are not the same

Question

I have an issue with a code that works perfectly when used in vba, but rewriting it in Python with Pyodbc raises an error. I simplified the code as much as I can as it is below. The code copies a table between two Access databases.

I made sure that both tables in both databases are the same so there is no issue. I believe the error may be either that INSERT INTO SELECTmay have different syntax in Pyodbc or that Pyodbc does not manage to copy between 2 databases while opening one, such as vba code. The python code with Database1 where table is copied from is:

conn = pyodbc.connect(
    r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\DB1.accdb;')
cursor = conn.cursor()   
cursor.execute(sqlStr)

The SQL code in sqlStr with Database2, where data is copied to is:

INSERT INTO Tab1
IN '' ';database=C:\DB2.accdb'
SELECT 
    Col1,
    Col2
FROM Tab1

The error is ('21S01', '[21S01] [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same. (-3520) (SQLExecDirectW)')

Is there any obvious mistake I am doing?

Please show how it works perfectly when used in vba.

Parfait
– Parfait

2022-09-03 20:47:56 +00:00
Commented Sep 3, 2022 at 20:47 — Parfait
– Parfait, Commented Sep 3, 2022 at 20:47

Gord Thompson · Accepted Answer · 2022-09-03 19:03:42Z

2

The destination table (in DB2.accdb) appears to have a different number of columns than the ones you are trying to insert. The Access UI (MSACCESS.EXE) seems to be okay with that, but Access ODBC doesn't like it.

You need to specify the columns in the destination table:

sql = r"""
INSERT INTO Tab1 (Col1, Col2)
IN 'C:\Users\Public\__tmp\DB2.accdb'
SELECT 
    Col1,
    Col2
FROM Tab1
"""

answered Sep 3, 2022 at 19:03

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

Honzik Over a year ago

Thank you very much, your advice solved the issue. Strange thing however is, that the number of columns is the same in both tables. Both tables are newly created and I made sure they are same. But specifying names of columns solved my problem.

Collectives™ on Stack Overflow

Python Pydobc SQL error - Number of query values and destination fields are not the same

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related