pythonms-accessodbc

pyodbc INSERT FROM CSV stopped working in Access - "Operation not supported for this type of object"

I recently (as of November 2025) had some working SQL via pyODBC to import an existing CSV file to a table in an Access database.

cursor.execute(f"""
INSERT INTO [{table_name}] ([Date], [Value], [Value2], [Year], [Month], [Day], [Value3])
SELECT [Date], [Value], [Value2], [Year], [Month], [Day], [Value3]
FROM [Text;Database={OUTPUT_PATH};HDR=YES].[{csv_file.name}]
""")
cnxn.commit()

However, rerunning the same method now, December 2025, the ODBC MS Access Database Driver throws an error that this operation is not supported for ''this type of object'', see below:

Error: ('HY000', '[HY000] [Microsoft][ODBC Microsoft Access Driver] Operation is not supported for this type of object. (-1001) (SQLExecDirectW)')

Has Microsoft permanently stopped supporting loading CSV/text files into Access Databases through a query like this or is this a machine-specific issue that I may be able to fix?

For context, the environment I am running uses Python 3.13.9, pyodbc version 5.3.0, MS Access DB Engine 2016 running on Windows 11 and am using r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' as the driver.

I am currently using a Pandas dataframe/cursor.executemany() as a workaround, however, it is orders of magnitude slower and fast_executemany = True causes my Python kernel to crash.

Thanks in advance!

Solution

  • It appears that Microsoft has addressed the issue in it latest security update

    This is from a couple years ago but it will fix the issue

    https://support.microsoft.com/en-us/topic/kb5002984-configuring-jet-red-database-engine-and-access-connectivity-engine-to-block-access-to-remote-databases-56406821-30f3-475c-a492-208b9bd30544

    I added the DWORD name: AllowQueryRemoteTables with value of 1 to registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft
    Office\16.0\Access Connectivity Engine\Engines

    The link I included will have more information about what registry path is needed.