0

I have some code which compacts and repairs a number of MS Access databases:

library(RDCOMClient)
library(stringr)

accfolders <- list.dirs('C:\\users\\username\\accessdb\\',recursive = FALSE,full.names=F)[-1] #need -1 to exclude current dir
accfolders <- paste0("C:\\users\\username\\accessdb\\",accfolders)

#launch access
oApp <- COMCreate("Access.Application")

for (folder in accfolders) {

  accfiles <- list.files(path=folder, pattern="\\.mdb", full.names=TRUE)

  print(paste("working in dir", folder))

  for (file in accfiles){

    print (paste("working in db", file))
    bkfile <- sub(".mdb", "_bk.mdb", file)

    oApp$CompactRepair(file, bkfile, FALSE)

    file.copy(bkfile, file, overwrite = TRUE)
    file.remove(bkfile)    
  }  

  #print(paste("completed", folder))
}

oApp$quit()
gc()

However, sometimes the code returns this following error:

<checkErrorInfo> 80020009 
Error: Exception occurred.

This error seems to happen somewhat randomly and it happens on the call oApp$CompactRepair during the second for loop

I can't seem to figure out why this happens and it happens with random .mdb files rather than a specific one. Sometimes I run the code and there is no issue at all, other times it produces the error.

Seeing as I can't figure it out, I'm wondering if I could capture this error somehow and just skip that element in the for loop? That way the code will not break down

user33484
  • 740
  • 2
  • 9
  • 36
  • 1
    Have you tried [`tryCatch`](https://stat.ethz.ch/R-manual/R-patched/library/base/html/conditions.html)? – r2evans Dec 16 '18 at 20:58
  • 2
    Possible duplicate of [Exception handling in R](https://stackoverflow.com/questions/2622777/exception-handling-in-r) – Erik A Dec 16 '18 at 20:58
  • I also recommend directly compacting using the database engine, e.g. `oApp <- COMCreate("DAO.DBEngine.120")` and `oApp$CompactDatabase(file, bkfile)`. In my experience, that's faster and less error-prone, though it's incompatible with some versions of Office 2016 when installed using the click-to-run installer instead of a normal full installation. – Erik A Dec 16 '18 at 21:03
  • It's not random. Check if files are being used; disk space of folder; type of folder (hopefully no internet ones like Google Drive, Dropbox, etc.); waiting for Compact to finish for larger databases (try adding `Sys.sleep(##)`); and other issues. And also, why are you compacting & repairing regularly in a loop of files? Consider your processing and not just programming. – Parfait Dec 17 '18 at 00:55
  • @Parfait yep, I think it's because there's an MS Access.exe still open in task manager. I usually have to quit that exe and run the code again in order for it to work. I'm not sure how to wait for compact to finish for larger DBs; Usually the code hangs whilst it's compacting/repairing. Any ideas? – user33484 Dec 17 '18 at 08:32
  • I mentioned using `Sys.sleep(##)` after the compact line but if R waits during loop, there is no need. – Parfait Dec 17 '18 at 15:13

0 Answers0