Query MS Access DB from R with 64-bit Windows

Question

Someone asked me to create a Shiny UI to allow them to read and write from their Microsoft Access database, created with MS Office 2010.

I was following this guide to connect to the Access DB with RODBC and -- while I can open this database in Access itself -- I get the following error from R:

> channel <- odbcConnectAccess("AD_Users.accdb")
Error in odbcConnectAccess("AD_Users.accdb") : `
  odbcConnectAccess is only usable with 32-bit Windows

So, I found this solution and gave it a try:

> channel <- odbcDriverConnect("AD_Users.accdb")
Warning messages:
1: In odbcDriverConnect("AD_Users.accdb") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect("AD_Users.accdb") :
  [RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC Driver Manager] Invalid connection string attribute
3: In odbcDriverConnect("AD_Users.accdb") : ODBC connection failed
> channel <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=AD_Users.accdb")
Warning messages:
1: In odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=AD_Users.accdb") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=AD_Users.accdb") :
  ODBC connection failed
>

but as you can see, there were some pretty serious warnings. It doesn't seem to work.

Is there any other work-around?

I am using 32-bit R when I receive these warnings and even though they're warnings and not technically errors, the database connection is unsuccessful.

Update

This seems to establish a successful connection, though I've been unable to query data from it thusfar:

channel <- odbcConnectDbase("AD_Users.accdb")

score 5 · Accepted Answer · answered Mar 22 '16 at 18:06

5

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!

answered Mar 22 '16 at 18:06

mfidino

3,030
1
9
13

Thanks, I didn't expect that function to be of use since it's 2010 instead of 2007, but it did work. – Hack-R Mar 22 '16 at 18:27

Query MS Access DB from R with 64-bit Windows

1 Answers1