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

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?

CodePudding user response：

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
"""