How to connect R with Access database in 64-bit Window?

Question

When I tried to connect R with Access database I get an error

odbcConnectAccess is only usable with 32-bit Windows

Does anyone has an idea how to solve this?

library(RODBC) 
mdbConnect<-odbcConnectAccess("D:/SampleDB1/sampleDB1.mdb")

Solution 1

Use odbcDriverConnect instead. If you have 64-bit R installed, you may have to use the 32-bit R build.

odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:/SampleDB1/sampleDB1.mdb")

Solution 2

I came across this SO when encountering a similar problem and at this point we have at least one more option with the extremely flexible odbc library.

An important note here though: the MS Access ODBC driver is not part of a default MS Office installation so you will have to download the appropriate driver from Microsoft (Microsoft Access Database Engine 2016 Redistributable in my case) and be sure to download the appropriate bitness (e.g. AccessDatabaseEngine_X64.exe). Once that has been downloaded it should automatically show up in your Windows ODBC Data Sources (64-bit) utility or you can confirm inside an R session with the odbcListDrivers function.

library(odbc)

# run if you want to see what drivers odbc has available
# odbcListDrivers()

# full file path to Access DB
file_path <- "~/some_access_file.accdb"

# pass MS Access file path to connection string
accdb_con <- dbConnect(drv = odbc(), .connection_string = paste0("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=",file_path,";"))