RODBC with ms-access error 07002 17 [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

Question

Problem:

I have a small database, for which MS Access works well. Using the RODBC package, I use sqlSave to create a new table with the data.frame in question. I tried to use sqlSave with append=TRUE to add the records directly to the table, but get a generic error that I can't append to that table, and I can't figure out why that's the case. So, I've built an INSERT sql-string in R, then use sqlQuery to insert the values from my intermediary table to the final table. Afterwards, the intermediary table is dropped from the DB. I have several functions which do this task to other tables in this way, and all of them work perfectly, it's only this one that doesn't want to behave.

I know that the sql-string works, because I can run that exact sql statement in Access directly with the two tables, and there are no issues. However, when I run the query within the context of the function I wrote I get the error 07002 17 [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect. As per this SO post and this thread from the MS developer forum, I looked to make sure I'm escaping any column names, and that I've referred to the columns correctly, and that data types match from my data.frame to the DB table. But, still not working.

Code:

# the devDB argument is the file path to the database on my machine, 
# and is defined in my session environment

insertFunction <- function(df, devDB){
  
  sqlStat <- "INSERT INTO tbl_Source ( ID, [Set], Source, [Source Desc], Type, [Age (d)], [On Product?], Formulation, [AB Program], [Date Rec] )
                SELECT intermediaryTable.ID, intermediaryTable.[Set], intermediaryTable.Source, 
                intermediaryTable.SourceDesc, intermediaryTable.Type, intermediaryTable.Aged, 
                intermediaryTable.OnProduct, intermediaryTable.Formulation, intermediaryTable.ABProgram, intermediaryTable.DateRec
                FROM intermediaryTable;"
  
  res <- tryCatch(
    {
      # establish the connection to whichever DB
      chan <- RODBC::odbcDriverConnect(connection = paste("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=", devDB, sep = ""), case = "nochange")
      
      # if the intermediary table remains from the last update, remove it from the DB
      if(any(grepl("intermediaryTable", RODBC::sqlTables(channel = chan)$TABLE_NAME))){
        RODBC::sqlQuery(chan, query = "DROP TABLE intermediaryTable;")
      }
      
      # save the data.frame as a table in the database
      RODBC::sqlSave(channel = chan, dat = df, tablename = "intermediaryTable", rownames = FALSE)

      # run the sqlStat char string from above to add the records, and save the 
      # output to log the update status in a log file
      dbUpdateStatus <- RODBC::sqlQuery(channel = chan, query = sqlStat)
    },
    error = function(cond){
      return(paste("Error occurred! ", cond, " timestamp:",Sys.time()))
    }
  )
  
  RODBC::sqlQuery(channel = chan, query = "DROP TABLE intermediaryTable;")
  RODBC::odbcClose(channel = chan)
  rm(chan)
  
  return(res)
}

Session Info:

R version 3.6.1 (2019-07-05)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows 10 x64 (build 18363)

Matrix products: default

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

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

other attached packages:
[1] magrittr_2.0.1

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.6       fansi_0.4.2      assertthat_0.2.1 utf8_1.1.4       crayon_1.4.1     dplyr_1.0.4     
 [7] R6_2.5.0         odbc_1.3.0       DBI_1.1.1        lifecycle_1.0.0  pillar_1.5.0     rlang_0.4.10    
[13] blob_1.2.1       vctrs_0.3.6      generics_0.1.0   ellipsis_0.3.1   RODBC_1.3-16     tools_3.6.1     
[19] bit64_4.0.5      glue_1.4.2       bit_4.0.4        purrr_0.3.4      hms_1.0.0        compiler_3.6.1  
[25] pkgconfig_2.0.3  tidyselect_1.1.0 tibble_3.1.0 

The ODBC driver used is Access Database Engine 2010, available here.

Reprex

Table Structure

table name: tbl_Source

• ID: Autonumber (long-int)
• Set: double
• Source: double
• Source Desc: short text
• Type: short text
• Age (d): double
• On Product?: short text
• Formulation: short text
• AB Program: short text
• Date Rec: Date/Time

Sample Data:

df <- data.frame(
  ID = c(12495:12497),
  Set = rep(998, 3),
  Source = c(1:3),
  SourceDesc = c("Desc 1", "Desc 2", "Desc 3"),
  Type = c("Type1", "Type2", "Type3"),
  Aged = c(28, 24, 5),
  OnProduct = c("No", "No", "Yes"),
  Formulation = rep(NA, 3),
  ABProgram = rep(NA, 3),
  DateRec = rep("04/01/2021", 3)
)

The sample data df shares column names that work with the insertFunction code at the top.

Please, let me know if there's anything else anyone else sees is missing for a reprex.

Thank you!

Answer 1

I've noted weirdness using RODBC before, and can reproduce the issue using RODBC, but DBI works fine. Since I see your environment contains both DBI and odbc, I highly suggest you use it. If I were to do a guess, I'd guess since RODBC has no separate execute function and something goes wrong there.

Let's translate your code:

library(DBI) 
# I've noted some peculiarities when not attaching DBI, though this code seems to run without it

insertFunction <- function(df, devDB){
  
  sqlStat <- "INSERT INTO tbl_Source ( ID, [Set], Source, [Source Desc], Type, [Age (d)], [On Product?], Formulation, [AB Program], [Date Rec] )
                SELECT intermediaryTable.ID, intermediaryTable.[Set], intermediaryTable.Source, 
                intermediaryTable.SourceDesc, intermediaryTable.Type, intermediaryTable.Aged, 
                intermediaryTable.OnProduct, intermediaryTable.Formulation, intermediaryTable.ABProgram, intermediaryTable.DateRec
                FROM intermediaryTable;"
  
  res <- tryCatch(
    {
      # establish the connection to whichever DB
      chan <- DBI::dbConnect(odbc::odbc(), .connection_string = paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=", devDB))
      
      # We don't need to remove the table when it exists, since we specify `overwrite = T`
      # save the data.frame as a table in the database
      # We need to specify batch_rows = 1 since Access does not support batch inserts through DBI
      DBI::dbWriteTable(chan, df, name = "intermediaryTable", batch_rows = 1, overwrite = T)

      # run the sqlStat char string from above to add the records, and save the 
      # output to log the update status in a log file
      dbUpdateStatus <- DBI::dbExecute(chan, sqlStat) # Note: this won't match RODBCs output
    },
    error = function(cond){
      return(paste("Error occurred! ", cond, " timestamp:",Sys.time()))
    }
  )
  # If the try-catch fails due to a connection problem, the following line will likely fail too, and the database connection won't close
  DBI::dbExecute(chan, "DROP TABLE intermediaryTable;")
  DBI::dbDisconnect(chan)
  rm(chan)
  
  return(res)
}

Note that it's generally preferable to append to the target table directly instead of writing to an intermediary and then appending, and DBI can do that with dbAppendTable.