Appending new data to a local Access data base file with r after a successful connection

Question

So I am currently working with a connecting to an Access database. I am able to get connected to the Access DB which is located on my local system. This is actually connected to a SharePoint list. I would love to automate the process handling this SharePoint list with an R and Access combo! What I want to be able to do actually pretty basic, I want to introduce new data via a .csv which is processed for the relevant content and then compared to the current Access DB and finally the new information uploaded from r to Access.

I've learned that you need to pair the bit version of your Windows OS, Office version, and R version. So I am x64 on all of the above. This allowed me to connect to the Access DB. You also need the 'Microsoft Access Database Engine 2016 Redistributable' which is essentially the driver for the connection.

So what I have so far is:

    library(odbc)
    library(DBI)   
    file_path <- "C:/user/Documents/R Projects/...pathtofile.../filename.accdb"

        accdb_con <- dbConnect(drv = odbc(), .connection_string = paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",file_path,";"))

        access.db <- dbReadTable(accdb_con, "sNPS Deep Dives")

That now connects! I then read in a .csv of new information

new.df <- read.csv("C:/user/Documents/R projects/...pathtofile.csv", header=T, stringsAsFactors=FALSE, na.strings=c("","NA"))

an example of the data set might just look something like this:

date <- c("15/10/2018","15/10/2018", "16/10/2018", "12/11/2018", "07/09/2018")
score <- c("6", "10", "7", "10", "9")
group <- c("a","b", "b", "a", "b")
CaseID <- c("301", "302", "303", "304", "305")

new.df <- data.frame(date,score,group,CaseID)
new.df$date <-  as.character(new.df$date)
new.df$score <- as.numeric(new.df$score)
new.df$group <- as.character(new.df$group)
new.df$CaseID <- as.numeric(new.df$CaseID)

Notably there are more columns in the Access DB that people will fill in by hand with further information.

and I process it to be ready go into the Access DB.

probably not that interesting...

Then I compare the the new data against the Access DB as such:

library(dplyr)    
new <- anti_join(new.df, access.db, by= "Case.ID")

Now I've tried:

dbWriteTable(access.db.copy, new, append = TRUE)
dbAppendTable(access.db.copy, new)

I don't seem to be able to get this to go anywhere

I am getting an error:

Error in (function (classes, fdef, mtable)  : unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"ACCESS", "data.frame", "missing"’

I've seen plenty of posts in which people are having trouble connecting to an Access DB but I haven't seen anything about writing new data into that database.

I know this isn't quite a reproducible example but it seems like a difficult problem to recreate since it's a connection problem between different tools. I would be happy to provide example sets that might make this easier

I would appreciate any direction you all can provide.

Thanks!

Edit:

It appears that Bing Sun was right, I was missing an argument. So it appears that we need something more like:

dbWriteTable(access.db.copy, "Name of table",new, append = TRUE)

Which produces the error:

Error in result_insert_dataframe(rs@ptr, values) : 
      nanodbc/nanodbc.cpp:1944: HY104: [Microsoft][ODBC Microsoft Access Driver]Invalid precision value 

I wonder if this may something that is an error from Access about a file type?

now if I use the append I don't get an error I get a 0 for output

 dbAppendTable(access.db.copy, "Name of table", new, append= TRUE)

With output:

[1] 0

But I don't see any of the new values when I check the Access file.

Answer 1

I know it's years later, but hopefully this will help someone else with this issue since you're right CrayCrayTown, there aren't very many posts covering this issue.

I've run into this problem repeatedly when dealing with R and MS Access. The solution that I've come up with is pretty "hacky" but it accomplishes what's trying to be done...just not very eloquently.

The way I do this is with a combo of RODBC and DBI packages.

First, I open a connection to the DB with RODBC, and use that connection to write my data to the DB as an intermediary table:

chan <- RODBC::odbcDriverConnection(connection = "/path/to/database.accdb")

RODBC::sqlSave(channel = chan, 
               dat = df, 
               tablename = "tbl_intermediary", 
               rownames = FALSE, 
               append = FALSE)

RODBC::odbcClose(chan)
rm(chan)

Make sure to close the RODBC connection, I also destroy it for good measure, because why not? I use RODBC for the intermediary table because it supports batch insert statements. I know that the same thing can, in theory, be done with DBI with DBI::dbAppendTable()(but we wouldn't be on this post if that worked how we had hoped). I tried this in a previous SO question here, but it didn't solve my problem. I also don't know how big my intermediary tables could get in the future. Hopefully by the time they get too big we'll be in a different DBMS.

Next, I reopen the connection, this time with DBI, and send a statement to the DB to write those data from the intermediary table to the final resting place for those data, and then drop the intermediary table.

con <- DBI::dbConnect(odbc::odbc(), .connection_string = "/path/to/database.accdb")

DBI::dbSendStatement(
  conn = con,
  statement = 'UPDATE 
                 tbl_intermediary INNER JOIN final_tbl ON tbl_intermediary.SampleID = final_tbl.sampleNumber 
               SET 
                 final_tbl.field1 = [tbl_intermediary].[field1],
                 final_tbl.notes = IIf(Nz([tbl_intermediary].[Notes],"")="",[final_tbl].[notes],[final_tbl].[notes] & "; Newest Notes: " & [tbl_intermediary].[Notes]);'
)

DBI::dbSendStatement(
  conn = con,
  statement = 'DROP TABLE tbl_intermediary;'

DBI::dbDisconnect(con)
rm(con)
)

The main reason why I chose this method is because some of the SQL I use with Access also has some VBA in it. When I send the SQL-VBA hybrid string with RODBC, I get assorted errors in the IIF() and Nz() functions (see example above). From the RODBC CRAN docs the query argument for the sqlQuery() function is strictly assumed to be a valid SQL statement. So, RODBC has no clue how to interpret the IIf() and Nz() MS Access functions. I think this also has to do with how the ODBC driver handles communication as well (please, someone correct me if I'm wrong about this).

As I understand it, DBI::dbSendStatment() however lets the database engine you're working with interpret how to use the statement argument you provide. In the situation above, the VBA is executed exactly how I would expect if it were run in Access directly. As per the DBI docs, for interactive use you'll generally want to use dbGetQuery or dbExecute.