I would like to use the RODBC package to partially overwrite a Microsoft Access table with a data frame. Rather than overwriting the entire table, I am looking for a way in which to remove only specific rows from that table -- and then to append my data frame to its end.
My method for appending the frame is pretty straightforward. I would use the following function:
sqlSave(ch, df, tablename = "accessTable", rownames = F, append = T)
The challenge is finding a function that will allow me to clear specific row numbers from the Access table ahead of time. The sqlDrop and sqlClear functions do not seem to get me there, since they will either delete or clear the entire table as a whole.
Any recommendation to achieve this task would be much appreciated!
Indeed, consider using sqlQuery to subset your Access table of the rows you want to keep, then rbind with current dataframe and finally sqlSave, purposely overwriting original Access table with append = FALSE.
# IMPORT QUERY RESULTS INTO DATAFRAME
keeprows <- sqlQuery(ch, "SELECT * FROM [accesstable] WHERE timedata >= somevalue")
# CONCATENATE df to END
finaldata <- rbind(keeprows, df)
# OVERWRITE ORIGINAL ACCESS TABLE
sqlSave(ch, finaldata, tablename = "accessTable", rownames = FALSE, append = FALSE)
Of course you can also do the counter, deleting rows from table per specified logic and then appending (NOT overwriting) with sqlSave:
# ACTION QUERY TO RUN IN DATABASE
sqlQuery(ch, "DELETE FROM [accesstable] WHERE timedata <= somevalue")
# APPEND TO ACCESS TABLE
sqlSave(ch, df, tablename = "accessTable", rownames = FALSE, append = TRUE)
The key is finding the SQL logic that specifies the rows you intend to keep.