2

I would like to save an R dataframe as a new table in an existing .mdb available here. I am getting an error message (below) and I suspect it has to do with .. ..$ supports.transactions: logi FALSE. I know I can save the dataframe as a .csv and import it into Access by hand. However, I would like my code to work for people who do not have Access software on their computer.

My connection is working and I'm able to dbReadTable from the .mdb. The .mdb I'm working with is I've tried

DBI::dbWriteTable(con,
                   "SSURGO_Soils_new",
                   value=newsoilexp)

as explained here, which results in

Error in new_result(connection@ptr, statement) : 
  nanodbc/nanodbc.cpp:1344: 42000: [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

I've tried

DBI::dbWriteTable(con,
                   SQL("SSURGO_Soils_new"),
                   value=newsoilexp)

as suggested here, which results in 

Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in%  : 
  nanodbc/nanodbc.cpp:4266: HYC00: [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented

Here is the DBI::dbGetInfo(con) output:

$dbname
[1] "C:\\SWAT\\ARCSWAT\\DATABASES\\SWAT_US_SSURGO_Soils_20190419.mdb"

$dbms.name
[1] "ACCESS"

$db.version
[1] "04.00.0000"

$username
[1] "admin"

$host
[1] ""

$port
[1] ""

$sourcename
[1] "mySSURGO"

$servername
[1] "ACCESS"

$drivername
[1] "ACEODBC.DLL"

$odbc.version
[1] "03.80.0000"

$driver.version
[1] "Microsoft Access database engine"

$odbcdriver.version
[1] "03.51"

$supports.transactions
[1] FALSE

attr(,"class")
[1] "ACCESS"      "driver_info" "list"

And my sessionInfo()

R version 3.5.3 (2019-03-11)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] dbplyr_1.4.2     DBI_1.0.0        odbc_1.1.6       car_3.0-3        carData_3.0-2    dplyr_0.8.3     
 [7] ggsci_2.9        cowplot_1.0.0    ggpubr_0.2.2.999 magrittr_1.5     ggthemes_4.2.0   ggExtra_0.8     
[13] devtools_2.1.0   usethis_1.5.1    ggplot2_3.1.0   

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.2        prettyunits_1.0.2 ps_1.2.1          zeallot_0.1.0     assertthat_0.2.0 
 [6] rprojroot_1.3-2   digest_0.6.18     mime_0.6          cellranger_1.1.0  R6_2.3.0         
[11] plyr_1.8.4        backports_1.1.4   httr_1.4.0        pillar_1.3.1      rlang_0.4.0      
[16] readxl_1.3.1      lazyeval_0.2.1    curl_3.2          rstudioapi_0.8    data.table_1.12.2
[21] miniUI_0.1.1.1    blob_1.2.0        callr_3.3.1       desc_1.2.0        labeling_0.3     
[26] stringr_1.3.1     foreign_0.8-72    bit_1.1-14        munsell_0.5.0     shiny_1.2.0      
[31] compiler_3.5.3    httpuv_1.4.5      pkgconfig_2.0.2   pkgbuild_1.0.4    htmltools_0.3.6  
[36] tidyselect_0.2.5  tibble_2.1.3      rio_0.5.16        crayon_1.3.4      withr_2.1.2      
[41] later_0.7.5       grid_3.5.3        xtable_1.8-3      gtable_0.2.0      scales_1.0.0     
[46] zip_2.0.3         cli_1.1.0         stringi_1.2.4     ggsignif_0.6.0    fs_1.3.1         
[51] promises_1.0.1    remotes_2.1.0     testthat_2.2.1    vctrs_0.2.0       openxlsx_4.1.0.1 
[56] tools_3.5.3       forcats_0.3.0     bit64_0.9-7       glue_1.3.0        purrr_0.2.5      
[61] hms_0.4.2         abind_1.4-5       processx_3.4.1    pkgload_1.0.2     yaml_2.2.0       
[66] colorspace_1.3-2  sessioninfo_1.1.1 memoise_1.1.0     haven_2.1.0

Will dbWriteTable() work properly if supports.transactions: TRUE? If so, how do I change it to TRUE? Or maybe something else is the problem. Neither the DBI or ODBC package manuals mention supports.transactions, so I suspect my problem is rather basic. Thanks in advance.

EDIT: This post does not address my question. That post uses the older pkg RODBC and deals with appending existing tables, not writing new ones.

BonnieM
  • 191
  • 1
  • 13
  • 1
    Possible duplicate of [Getting data from dataframe to MS-access DB Target Tables using R](https://stackoverflow.com/questions/34001509/getting-data-from-dataframe-to-ms-access-db-target-tables-using-r) – Rene Aug 22 '19 at 04:29
  • Note that creating new tables from R may not be feasible without either using COM or manually writing an SQL statement. – Erik A Aug 23 '19 at 08:50
  • @Rene I edited the post to explain why it is not a duplicate. – BonnieM Aug 23 '19 at 14:21
  • @Erik, can you expand a bit on your comment please? I don't know what using COM or manually writing an SQL statement really means? – BonnieM Aug 23 '19 at 14:21
  • See the duplicate. It both addresses writing SQL statements (you'd just need to execute a `CREATE TABLE` statement before your `UPDATE` statement), and using COM via `RDCOMClient`. Both require adjusting the code for the exact data types you want. – Erik A Aug 23 '19 at 14:41
  • Oh, and by the way, _I would like my code to work for people who do not have Access software on their computer_ is not what your current code will do. It requires an install of either Access or the Access Database Engine, and that install must match bitness with the R install. If you'd migrate to SQLite, the R part would get way easier, and you could link the tables in Access using an ODBC driver. – Erik A Aug 23 '19 at 14:46
  • @ErikA the "duplicate" deals with the RODBC package which only works with 32-bit Rstudio, I'm using 64-bit. I'm not sure where in the process COM comes into play if I already have the `con` set up. I tried SQLite with `dbPath <- "C:/....mdb" drv <- dbDriver("SQLite") db <- dbConnect(drv, dbPath) dbListTables(db)` but got `Error: file is not a database`. I am not clear on how SQLite is different from using DBI, ODBC, and dbplyr. I'm really new to this! Can you write example code for what you're thinking? – BonnieM Aug 26 '19 at 16:08
  • @ErikA also, thanks for the tip on my code not working for people who do not have Access software. If they are able to download the Access Database Engine (free) will it still work without Access? I also think my code might only work on Windows not MacOS? – BonnieM Aug 26 '19 at 16:13

0 Answers0