db_-access-implementation.R

db_write_table.ACCESS <- function(con, table, types, values,
                                  temporary = FALSE, ...) {

  db_create_table(con, table, types, temporary = temporary)

  # Convert factors to strings
  is_factor <- vapply(values, is.factor, logical(1))
  values[is_factor] <- lapply(values[is_factor], as.character)

  # Encode special characters in strings
  is_char <- vapply(values, is.character, logical(1))
  values[is_char] <- lapply(values[is_char], encodeString, na.encode = FALSE)

  tmp <- tempfile(fileext = ".csv")
  utils::write.table(values, tmp, sep = ",", quote = FALSE, qmethod = "escape",
                     na = "<NA>", row.names = FALSE, col.names = FALSE)

  sql <- paste0("INSERT INTO `", table, "` ",
                "SELECT * FROM [Text;FMT=Delimited;HDR=NO;DATABASE=", dirname(tmp), "].", basename(tmp))

  dbExecute(con, sql)

  invisible()
}

db_copy_to.ACCESS <- function(con, table, values, overwrite = FALSE, types = NULL,
                              temporary = FALSE, unique_indexes = NULL, indexes = NULL,
                              analyze = TRUE, ...) {

  # Temporary tables do not exist in Access
  if(temporary) {
    stop("Temporary tables do not exist in Access SQL")
  }

  # dbWriteTable does not yet work with Access connections. This is an issue that has been
  # filed over on the odbc package GitHub page

  if (overwrite) {
    db_drop_table(con, table, force = TRUE)
  }

  db_write_table(con = con,
                 table = table,
                 types = types,
                 values = values,
                 temporary = temporary)

}

db_drop_table.ACCESS <- function(con, table, force = FALSE, ...) {

  sql <- build_sql("DROP TABLE ", sql(table), con = con)

  # If you definitely want to drop, do it
  if(force) {
    DBI::dbExecute(con, sql)

  # If you haven't specified, make them if the table exists
  } else {

    if(table %in% dbListTables(con)) {
      cat("Are you sure you want to drop '", table, "'? Use `force = TRUE` if so.", sep = "")

    # Otherwise attempt to drop, but the table does not exist
    } else {
      DBI::dbExecute(con, sql)
    }
  }

}

Updated version with auto type detection

db_write_table.ACCESS <- function(con, table, types, values,
                                  temporary = FALSE, ...) {
  
  db_create_table(con, table, types, temporary = temporary)
  
  # Convert factors to strings
  is_factor <- vapply(values, is.factor, logical(1))
  values[is_factor] <- lapply(values[is_factor], as.character)
  
  # Encode special characters in strings
  is_char <- vapply(values, is.character, logical(1))
  values[is_char] <- lapply(values[is_char], encodeString, na.encode = FALSE)
  
  tmp <- tempfile(fileext = ".csv")
  utils::write.table(values, tmp, sep = ",", quote = FALSE, qmethod = "escape",
                     na = "<NA>", row.names = FALSE, col.names = FALSE)
  
  sql <- paste0("INSERT INTO `", table, "` ",
                "SELECT * FROM [Text;FMT=Delimited;HDR=NO;DATABASE=", dirname(tmp), "].", basename(tmp))
  
  dbExecute(con, sql)
  
  invisible()
}

db_copy_to.ACCESS <- function(con, table, values, overwrite = FALSE, types = NULL,
                              temporary = FALSE, unique_indexes = NULL, indexes = NULL,
                              analyze = TRUE, ...) {
  
  # Temporary tables do not exist in Access
  if(temporary) {
    stop("Temporary tables do not exist in Access SQL")
  }
  
  # dbWriteTable does not yet work with Access connections. This is an issue that has been
  # filed over on the odbc package GitHub page
  
  if (overwrite) {
    db_drop_table(con, table, force = TRUE)
  }
  
  # Automatically get types
  if(is.null(types)) {
    types <- purrr::map(values, class) %>%
      purrr::imap_chr(~c(.y = sql_type_convert_access(.x)))
  }
  
  db_write_table(con = con,
                 table = table,
                 types = types,
                 values = values,
                 temporary = temporary)
  
}

db_drop_table.ACCESS <- function(con, table, force = FALSE, ...) {
  
  sql <- build_sql("DROP TABLE ", sql(table), con = con)
  
  # If you definitely want to drop, do it
  if(force) {
    DBI::dbExecute(con, sql)
    
    # If you haven't specified, make them if the table exists
  } else {
    
    if(table %in% dbListTables(con)) {
      cat("Are you sure you want to drop '", table, "'? Use `force = TRUE` if so.", sep = "")
      
      # Otherwise attempt to drop, but the table does not exist
    } else {
      DBI::dbExecute(con, sql)
    }
  }
  
}

sql_type_convert_access <- function(class) {
  
  if(any(class %in% c("POSIXct", "POSIXt", "Date", "hms"))) {
    class <- "datetime"
  }
  
  switch(class,
         "integer" = "Integer",
         "double"  = "Double",
         "numeric" = "Double",
         "datetime"= "Datetime",
         "logical" = "Bit",
         "character" = "Varchar",
         "list" = "Integer",
         "factor" = "Varchar",
         stop("Unsupported type", call. = FALSE)
         )
}