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

Question

我正在尝试使用取自 this other question 的以下函数从 R 读取 Microsoft Access db.accd 数据库:

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。我已经安装了the drivers for Microsoft Access并使用数据源 (ODBC) 创建了数据库文件:

但是还是读不到数据库。

有什么提示吗？

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)