1

I've spent some time using the RDBOC library to write a data frame to an Access table but gave it up after realizing that it suppresses white spaces from column names.

Now I'm trying the dboc library, for which I've found fewer references on the web.

The connection to the database is created by writing:

con <- dbConnect(odbc(), .connection_string = paste("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=", my_db_path, sep = ""))

After that I tried two alternatives for writing the data frame to a table:

dbWriteTable(con, "existing_table", my_df, append = TRUE)
dbWriteTable(con, "existing_table", my_df, overwrite = TRUE)

Both returned the error below. Column names in the data frame match the column names in the table.

Error: nanodbc/nanodbc.cpp:1763: HYC00: [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented

Any guidance on how to debug this error?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
SuavestArt
  • 193
  • 1
  • 6
  • 1
    rodbc may be trying to send integer values as BIGINT, which Access ODBC does not support. To check that, [enable ODBC tracing](https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log#windows) and run your (failing) code. Turn ODBC tracing off again, then open the SQL.LOG file and search for `SQL_BIGINT`. If you find it then check the rodbc documentation to see if you can tell it to send integers as SQL_INTEGER instead of SQL_BIGINT. – Gord Thompson Aug 30 '23 at 21:39

1 Answers1

2

DBI with odbc batches inserts by default. Access doesn't support batch inserts. That's the optional feature that isn't implemented.

To do normal inserts, specify batch_rows = 1:

dbWriteTable(con, "existing_table", my_df, append = TRUE, batch_rows = 1)

Also discussed on GitHub.

Regarding debugging, ODBC tracing is generally a good move, as Gord Thompson noted, but this is a known issue.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • It works, thank you, but it's too slow. I even tried to write to an Access file in my desktop rather than on the network, but this didn't improve speed. Any guess? – SuavestArt Aug 31 '23 at 14:24
  • 1
    Well... Access just isn't that fast. The details really depend on exactly what you're inserting. It can be a good idea to write the data to Excel first, then import the data from Excel into Access. See https://stackoverflow.com/a/70977554/7296893 – Erik A Aug 31 '23 at 15:33