Cannot read microsoft access database in R under Windows 10 (x64)

Question

I am trying to read a Microsoft access db.accd database from R using the following function took from this other question:

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)

However I get the error:

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

I am using R 3.5.1 x64 on 64 bits Windows. I have installed the drivers for Microsoft Access and created the database file using the data origin (ODBC):

But still cannot read the database.

Any hints?

The link to the engine driver was a 32-bit and if you had a 64-bit office then it would not work — Ibo, Jan 27 '20 at 21:27

score 1 · Answer 1 · answered Apr 05 '19 at 01:00

Try using odbcConnectAccess2007, for example:

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)

Cannot read microsoft access database in R under Windows 10 (x64)

1 Answers1