1

I want to export Tables from an Access database to .txt-files.

Here is my Code:

library(RDCOMClient)
#Path of txt File
destPath = 'C:\\Path\\to\\Hello.txt'
#Path of AccessDB
strDbName = "C:\\MyPath\\AccessDB.accdb"
#launche Access App
oApp = COMCreate("Access.Application")
#open the AccessDB
oApp$OpenCurrentDatabase(strDbName)
#Export the table to txt using transferText Method
acExportDelim  <- 0
exportObj = oApp[["DoCmd"]]
exportObj$TransferText(acExportDelim,"NameOfTable", destPath, TRUE)
oApp$Quit()
exportObj <- NULL
oApp <- NULL

I cant find out why it doesnt work...

This is the error Message i get all the time: 

<checkErrorInfo> 80020009 
Error: exception occurred.

Any help appreciated! (The "RODBC approach" using 32bit R doesnt work for the entity i need)

Thanks in advance...

sneakyErich
  • 66
  • 7
  • *The "RODBC approach" using 32bit R doesnt work for the entity i need* ... what is your bit version of R (`Sys.info()$machine`)? What is your bit version of MS Office (open Access or any Office app, in back stage, click Account \ About button, read top line)? – Parfait Oct 18 '17 at 19:19
  • At the moment Im doing this: -Launching Rscript.exe (32bit) with (system()) - run the script with the sql querys there and save the tables as .RData -Load the .RData Files into my global Env. The problem is, that for only one of all the tables, it doesnt work. The default settings in Access for one of the Attributes of this table is to set a standard value... I think when im getting the table with my script, the standart value gets into the last "empty" row and so, the table looses its consistency... A front end Error occurrs and the Rscript.exe breaks down... – sneakyErich Oct 19 '17 at 06:59

1 Answers1

1

The arguments of your DoCmd.TransferText are incorrect. The second argument, where you've put the table name, should be the name of the export specification. Because you've got that wrong, all other arguments are wrong too.

First, create a named export specification in Access (guide). Then, use that in your command to export the table.

Also, you can't use the acExportDelim enum since you're using late bindings, so you will have to use the numeral for that value (2)

Final export command:

oApp[["DoCmd"]]$TransferText(2,"ExportSpecificationName", "TableName", destPath, TRUE)

Note that you're probably better off trying to fix ODBC connectivity, if possible.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • 1
    OP can also leave export specification blank: `""` – Parfait Oct 18 '17 at 19:17
  • @Parfait That may or may not be true depending on locale settings, see [this question](https://stackoverflow.com/questions/26991172/transfertext-export-to-csv-not-working-but-transferspreadsheet-to-xlsx-working). To be safe, I suggest using one. – Erik A Oct 18 '17 at 19:45
  • Thanks a lot for your answer Erik. Its working :)) @Parfait thanks for the tip with the blank specification... Its working as well ! now its just the perfect solution for me. you guys are the mvp's of the day :D – sneakyErich Oct 19 '17 at 07:07