How to Connect R with Access Database in 64-Bit Window

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

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

Query MS Access DB from R with 64-bit Windows

I use odbcConnectAccess2007 in RODBC and don't have any issues connecting to Access databases when using 64-bit windows. However, you should check the package manual (link to pdf) to make sure that you have the appropriate drivers installed on your computer. Once you have the right drivers you should be good to go!

Connect R to MS Access 64bit Windows

You can use my AceOdbcCheck script to test your machine for a valid install of Access "ACE" ODBC. If you don't have it installed you can download the drivers here.

Note that the "bitness" (64-bit or 32-bit) of the Access ODBC driver must match the version of R you are running. That is, if you are running 32-bit R then you need the 32-bit drivers even though you are running 64-bit Windows.

Connect MS Access database to R

Is "BASE_MEPSA" the name of the Data Source Name (DSN)? You would have created this in your ODBC Data Source Administrator. If so, I can always successfully connect with:

library(DBI) 
cn <- dbConnect(odbc::odbc(), dsn="BASE_MEPSA")

Additionally, you need to make sure that you are using the same architecture. This means that you should be using the x32 versions of R, Access, and ODBC Data Source Admin if your Access version is x32. You have to change this in the global settings for R as the default is x64.

Also try using the file path you used for RODBC.

cn <- dbConnect(odbc::odbc(), DBQ="C:/Users/IPS/Desktop/divers/dt/stateduc_R/BASE_MEPSA.mdb")

How to connect to .accdb database from R

The code you're using is fine, your setup isn't.

You need to either use 64-bits R and 64-bits MS Access, or 32-bit R and 32-bit MS Access. You can try, however, to install 64-bits Access Database Engine, found here (2016 version) or here (2010 version).

I have had varying results with installing both the full version of Access and the database engine (currently got Access 32-bits 2016, and couldn't install Access database engine 64-bits 2016, but could install the 2010 version by using the /passive command on the installer).

See this blog for some details on how to install the 32-bits full version and the 64-bits database engine on one machine. But as noted, results may vary.

Reading data from 32-bit Access db using 64-bit R

The access file itself knows nothing about bitness, its only about the client application and the bitness of the odbc driver:

If your R is 64 bit, you need the 64bit ODBC driver for access and therefore also use the odbc manager for 64bit, which is C:\Windows\System32\odbcad32.exe (in Win7 64bit).

While if your R is 32bit, you need the 32bit ODBC driver, located at C:\Windows\SysWOW64\odbcad32.exe.

You can download the required Access Database Engine 2010 Redistributable from here: https://www.microsoft.com/en-US/download/details.aspx?id=13255

So, download the 64 bit Access Database driver, create a 64bit DSN entry and you should be fine.

Access 2013 database read in R through RODBC

MS Access is a file-level DBMS. Hence, you need to specify the location of your database. The default MS Access Database DSN alone will not suffice. Without specifying the file, your error is reproduced.

conn <- odbcConnect("MS Access Database;DBQ=C:\\Path\\To\\Database.accdb")
df <- sqlQuery(conn, "SELECT * FROM myTable")
odbcClose(conn)

Now if you did create your own user-defined DSN with the .mdb/.accdb file specified, you certainly can connect with it alone.

conn <- odbcConnect("myDSN")
df <- sqlQuery(conn, "SELECT * FROM myTable")
odbcClose(conn)

---

Related Topics