1

Update: I tried running the script from another computer with an earlier version of sqlalchemy-access (1.1.2 instead of 1.1.3) the script worked. When I installed 1.1.2 on the computer that gave the error, I got

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:access.pyodbc

Both computers are updating the same access table (which is saved on a network drive).

I am updating an existing MS Access table using a dataframe (all_results_df):

cnn_str = r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\data\MyData.accdb;'
cnn_url = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(cnn_str)}"
acc_engine = create_engine(cnn_url)
all_results_df.to_sql('results', acc_engine, if_exists='append')

and I get the following error:

ProgrammingError: ('42S01', "[42S01] [Microsoft][ODBC Microsoft Access Driver] Table 'results' already exists. (-1303) (SQLExecDirectW)")

Why doesn't it just append the new data to the existing table?

Nitzan
  • 33
  • 5
  • from [this issue at github](https://github.com/mkleehammer/pyodbc/issues/269) issue it appears the problems is that MSAccess needs a confirmation (or to have the alerts disabled - which probably can't be done in pyodbc). You might be able to disable alerts on the database as an option. Otherwise you might pursue the same workaround mentioned there. – topsail Jul 03 '22 at 15:04
  • 1
    @topsail, those messages are part of the MSAccess.exe software not the Access database engine. Recall "Access" is a [multifaceted thing](https://meta.stackexchange.com/questions/33216/ms-access-or-mdb-or-access-database-engine-or-ms-jet-ace) that can be accessed on frontend with the Office app or backend via ODBC/OLEDB. Action DML or DDL queries via backend connection as OP attempts do not require user confirmation. – Parfait Jul 03 '22 at 15:12
  • Please post the `dtypes` of data frame and table design of `results` table. We need to see your table column fields for any special data types. – Parfait Jul 03 '22 at 15:14
  • Is the table really named `results`? If the table name actually contains accented characters then you may have run into [this issue](https://github.com/gordthompson/sqlalchemy-access/issues/17), which was fixed in sqlalchemy-access version 1.1.3. – Gord Thompson Jul 03 '22 at 15:18
  • I updated the question with additional information. – Nitzan Jul 05 '22 at 06:46
  • @Parfait the ```dtypes``` are all either object or float64. @Gord Thompson The table is actually named ```bat_results```, so no accented characters. – Nitzan Jul 05 '22 at 07:03

1 Answers1

1

Eventually, updating ms-access solved the problem.

Nitzan
  • 33
  • 5