r - 在 Windows 10 (x64) 下无法读取 R 中的 microsoft access 数据库

Question

我正在尝试使用从另一个问题db.accd中获取的以下函数从 R 中读取 Microsoft 访问数据库：

require(DBI)
library(RODBC)
db_file_path="db.accd"
dbq_string <- paste0("DBQ=", db_file_path)
driver_string <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
db_connect_string <- paste0(driver_string, dbq_string)

con <- odbcDriverConnect(db_connect_string, rows_at_time = 1)
table <- sqlQuery(con, "select * from TABLE", stringsAsFactors = FALSE)

odbcClose(con)

但是我得到了错误：

Warning messages:
1: In odbcDriverConnect(db_connect_string, rows_at_time = 1) :
  [RODBC] ERROR: state HY000, code 63, message [Microsoft][Controller ODBC Microsoft Access]Error general cannot open the key 'Temporary (volatile) Ace DSN for process 0x66c Thread 0xff0 DBC 0x26d3098                                                              Jet' of Registry.
2: In odbcDriverConnect(db_connect_string, rows_at_time = 1) :
  [RODBC] ERROR: state HY000, code 63, message [Microsoft][Controller ODBC Microsoft Access]Error general cannot open the key 'Temporary (volatile) Ace DSN for process 0x66c Thread 0xff0 DBC 0x26d3098                                                              Jet' of Registry.
3: In odbcDriverConnect(db_connect_string, rows_at_time = 1) :
  [RODBC] ERROR: state HY000, code -1811, message [Microsoft][Controller ODBC Microsoft Access] cannot find the file '(unknow)'.
4: In odbcDriverConnect(db_connect_string, rows_at_time = 1) :
  ODBC connection failed 

我在 64 位 Windows 上使用 R 3.5.1 x64。我已经安装了 Microsoft Access 的驱动程序并使用数据源 (ODBC) 创建了数据库文件：

但仍然无法读取数据库。

有什么提示吗？

标签： rms-access

Answer 1

尝试使用odbcConnectAccess2007，例如：

library(RODBC)

# This will be your access database filename
db <- "db.accdb"
con <- odbcConnectAccess2007(db)

# Replace Table1 with the name of the table that you have in your access db
df <- sqlQuery(con, "SELECT * from Table1", as.is = TRUE)

odbcClose(con)