r-ROBC出现ms访问错误07002 17[Microsoft][ODBC Microsoft access Driv

Question

问题：

我有一个小数据库，MS Access对它工作得很好。使用RODBC包，我使用sqlSave创建一个包含问题中的data.frame的新表。我尝试将sqlSave与append=TRUE一起使用，将记录直接添加到表中，但出现了一个一般性错误，我无法将其附加到该表中，我不明白为什么会出现这种情况。因此，我在R中构建了一个INSERTsql字符串，然后使用sqlQuery将中间表中的值插入到最终表中。然后，将中间表从DB中删除。我有几个函数以这种方式对其他表执行这项任务，它们都能完美地工作，只有这一个不想表现出来。

我知道sql字符串是有效的，因为我可以在Access中直接使用这两个表运行精确的sql语句，并且没有任何问题。但是，当我在我编写的函数的上下文中运行查询时，我会得到错误07002 17 [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect。根据这篇SO文章和MS开发者论坛上的这篇帖子，我希望确保我正在转义任何列名，并且我已经正确引用了列，并且数据类型从我的data.frame到DB表匹配。但是，仍然不起作用。

代码：

# 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)
}

会话信息：

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 

使用的ODBC驱动程序是Access Database Engine 2010，可在此处获得。

Reprex

表格结构

表名：tbl_Source

• ID:Autonumber(long-int(
• 套装：双人
• 来源：double
• 源描述：短文本
• 类型：短文本
• 年龄(d(：双
• 关于产品？：短文本
• 公式：短文本
• AB程序：短文本
• 日期记录：日期/时间

样本数据：

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)
)

示例数据df共享与顶部的insertFunction代码一起使用的列名。

请告诉我，如果有其他人看到的代表缺少什么。

谢谢！

Answer 1

我以前曾注意到使用ROBC的怪异之处，并且可以使用ROBC重现该问题，但DBI运行良好。由于我看到您的环境同时包含DBI和odbc，我强烈建议您使用它。如果我进行猜测，我会猜测，因为ROBC没有单独的执行函数，并且出现了问题。

让我们翻译您的代码：

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)
}

请注意，通常最好直接追加到目标表，而不是写入到中介然后追加，DBI可以使用dbAppendTable来完成这一操作。