4

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'")
Parfait
  • 104,375
  • 17
  • 94
  • 125
shri
  • 41
  • 1
  • 2

1 Answers1

4

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
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I have used the below code its is missing some records i have 8000 records but only 7800 got inserted to db. any reason for that # 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) – Praveen DA Apr 11 '17 at 14:05
  • Check the data types of R compared to data types in Access table. Access silently ignores such mismatches without raising errors. – Parfait Apr 11 '17 at 18:38
  • Looks like this is due to Special character in the string eg:('\\\\.\\ROOT\\CIMV2' \\n \\n...''." ). – Praveen DA Apr 11 '17 at 20:31