Connect MS Access to R via ODBC Driver

0
votes

I cannot successfully connect MS Access to R. While reading other threads on stack overflow, I followed the suggestions of using 32-bit Access, changing Rstudio to 32-bit, and using the microsoft Access odbc Driver. I am not sure what to do further...

I used the following instructions:

  • Open ODBC Data Sources (32-bit) from windows start menu
  • Under System DSN tab, add driver Microsoft Access Driver (*.mdb, *.accdb), name driver MS Access Driver, click "OK"
  • In Rstudio, under tools,select Global Options, change R version to:[Default] [32-bit] C:\Program Files\R\R-3.5.1
  • In Rstudio, write the following script:

library(RODBC)

Data <- odbcDriverConnect("Driver={MS Access Driver}; DBQ=C:/Users/Owner/Desktop/Database1.accdb"

The warning message I receive reads:

Warning messages: 1: In odbcDriverConnect("Driver={MS Access Driver};DBQ=C:/Users/Owner/Desktop/Database1.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={MS Access Driver2};DBQ=C:/Users/Owner/Desktop/Database1.accdb") : ODBC connection failed

Please, any help would be appreciated. I was not able to glean enough information solely from other R to Access connection threads.

1
rms-accessodbc32-bit
Are you certain that the driver name matches one of the expected names? If you run odbc::odbcListDrivers(), do you see something like "Microsoft Access Driver (*.mdb, *.accdb)"? I think that's what should be used inside the Driver={...} portion of your connection string, btw. – r2evans
Side rant: it really drives me NUTS that I cannot install the 64-bit MSAccess odbc drivers on windows without completely killing the 32-bit installation of MSOffice. The fact that they make it impossible (or at least improbable) is frustrating. (Granted, their whole ODBC stack has self-injected data-crippling bugs, too, that haven't been fixed since first report 10 years ago.) – r2evans
Wow! I am ecstatic and embarrassed that it was that simple. Thank you so much for your help! – Milo S
This is actually my first time working with Access. I worked mostly with SQL Server and MySQL. However, that does seem odd and extremely frustrating. – Milo S
Trust me, if that's the most difficult of your issues conducting data transfers with MSAccess over ODBC, count yourself lucky. At one point I had a choice to stick-with-it and try to get something to work in Access, or plan B. I went with plan B, which involves exporting the tables to Excel and doing copy/paste from there. I really hate that type of solution, but the right-way-to-go does not work. – r2evans

1 Answers

0
votes

This is how I do it.

library(RODBC)

# for 32 bit windows
# Connect to Access db
channel <- odbcConnectAccess("C:/path_to_db/Northwind.mdb")

# Get data
data <- sqlQuery( channel , paste ("select * from Name_of_table_in_my_database"))