Getting data from dataframe to MS-access DB Target Tables using R

Question

I have created Data frames in R. I have connected to access DB using below code. I am able to extract all data from access db to R.

I have 3 tables in Access like A , B , C

Created same data frames in R like A, B, C

How to move the R data frames of A, B, C from R to Access DB Target Tables A,B,C?

Using R coding in R studio.?

Please help on this, this is important task for me.

con <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};
                          DBQ=C:/Users/chintan/Desktop/Published.accdb")

# Get data
Pub <- sqlQuery(con, "select * from certifications
                      Where [Business Group]='EG'
                      AND FY='FY15'")

Answer 1

Please take a look at the RODBC manual. There are multiple ways to append data frames to Access tables. Of course be sure data frame columns align to table columns by name and data type.

sqlUpdate (Write or update a table in an ODBC database)

sqlSave(con, pub, AccessTablename, append = FALSE, rownames = FALSE)

sqlUpdate(con, pub, Accesstablename)

sqlQuery (Submit an SQL query to an ODBC database, and retrieve the results)

# APPEND QUERY
sql <- paste0("INSERT INTO AccessTableName(col1, col2, col3) 
               VALUES('", pub$col1 ,"','", pub$col2, "','",pub$col3, "')")

appendRecords <- lapply(sql, function(x) sqlQuery(con, x))


# UPDATE QUERY
sql <- paste0("UPDATE AccessTableName 
                  SET col1 = '", pub$col1 ,"'
                      col2 = '", pub$col2 ,"'
                      col3 = '", pub$col3 ,"'")

appendRecords <- lapply(sql, function(x) sqlQuery(con, x))

---

You can even mirror VBA using RDCOMClient and export data frames to csv by opening an Access application object (with DoCmd property) and run the DoCmd.TransferText method.

library(RDCOMClient)

# OUTPUT R DATA FRAME TO CSV
rdfpath = 'C:\\Path\\To\\R\\DataFrame\\Output.csv'
write.csv(pub, rdfpath, row.names=FALSE)

# LAUNCH ACCESS APP
strDbName = 'C:\\Path\\To\\Database\\File.accdb'
oApp = COMCreate("Access.Application")
oApp$OpenCurrentDatabase(strDbName)

# IMPORT CSV DATA TO TABLE
acImportDelim  <- 0
importObj = oApp[["DoCmd"]]
importObj$TransferText(acImportDelim, "", "AccessTableName", rdfpath, TRUE)

importObj$CloseDatabase
importObj <- NULL
oApp <- NULL