rms-accessodbcrodbc

Write a data frame to an existing Access (.accdb) table in R

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?

Solution

  • 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.