how to download all MS access attachments using R

Question

I have around 100 rows in MS Access, each row includes an attachment. Using RODBC in R I can access the table but have no idea how to access attached files and download them in R

could you plz help me how to do it

Answer 1

Attachments in MS Access are special data types that actually involve nested tables for metadata (filename and filedata) information. Hence, you cannot access this data with DML SQL statements alone via RODBC but can using a COM interface, specifically connecting to the DAO SaveToFile() method.

Consider the following using the RDCOMClient package which allows interfacing to the Access Object Library. Do note: in order to run the below code, you must have MSAccess.exe (the MS Office GUI program) installed and not just the .accdb file. In the SQL query below, ColAttach is the name of the attachment field in your table and you must use those qualifiers .filedata and .filename. DAO recordset field numbers are zero based (hence the 0 and 1).

library(RDCOMClient)

# INITIALIZING OBJECTS
accApp <- COMCreate("Access.Application")
accApp$OpenCurrentDatabase("C:\\Path\\To\\Database.accdb")
docmd <-  accApp[["DoCmd"]]
db <- accApp$CurrentDb()

rst <- db$OpenRecordset("SELECT ColAttach.filedata, ColAttach.filename FROM TblAttach")

while(rst$EOF() == FALSE){     
  rst$Fields(0)$SaveToFile(paste0("C:\\Path\\To\\Output_", rst$Fields(1)$Value()))      
  rst$MoveNext()
}

# CLOSING OBJECTS
rst$close()
docmd$CloseDatabase()
accApp$Quit()

# RELEASING RESOURCES
accApp <- db <- docmd <- rst <- NULL    
rm(rst, db, accApp)
gc()

---

For multiple attachments, loop through the child recordset on each filename and filedata values (notice the different SQL). Be sure to check if file exists and destroy accordingly else you will receive a COM error:

rst <- db$OpenRecordset("SELECT ID, ColAttach FROM TblAttach")

while(rst$EOF() == FALSE){    
  childRS <- rst[['ColAttach']]$Value()

  while(childRS$EOF() == FALSE){
    if (file.exists(paste0("C:\\Path\\To\\Output_", childRS[["filename"]]$Value()))) {
      unlink(paste0("C:\\Path\\To\\Output_", childRS[["filename"]]$Value()))
    }

    childRS[["filedata"]]$SaveToFile(paste0("C:\\Path\\To\\Output_", 
                                     childRS[["filename"]]$Value()))
    childRS$MoveNext()
  }

  rst$MoveNext()
}