odbcConnect with Access database difference between R and RStudio

Question

I can use the following code successfully in the R GUI to connect to a Microsoft Access database:

library(RODBC)

#ODBC connection
scallopchannel<-odbcDriverConnect("scallop_2016")

"scallop_2016" is a DSN for the Access database I connect to.

In RStudio I have to specify to path to the Access database like this:

ODBC connection

scallopchannel<-odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=S://Adv/Scallop Central/2015 RSA/2015 RSA Database/2015 RSA Database_9-4-2015.accdb")

Otherwise I get an error message:

  Warning messages:
1: In RODBC::odbcDriverConnect("DSN=db2017") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In RODBC::odbcDriverConnect("DSN=db2017") : ODBC connection failed

When I type to troubleshoot the problem

odbcDataSources ()

RStudio indicates that the Access datasource is there.

This is a pain since I sometimes connect to multiple databases within a session and I prefer to be able to code for just the DSN.

I am using RStudio 1.0.153 and R GUI 3.3.2. I am also using 32 bit R and 32 bit RStudio.

Are you running 32-bits or 64-bits R, and 32-bits or 64-bits R Studio? (See https://stackoverflow.com/questions/18091614/how-can-i-know-if-r-is-running-on-64-bits-versus-32 to check which) — Erik A, Oct 30 '17 at 14:17
Are you sure about RStudio? The default installation uses 64-bits R (even if you only have the 32-bits variant installed, and even if the RStudio program is 32-bits itself), I put it in a bit of effort for my setup to ensure it's 32-bits. — Erik A, Oct 30 '17 at 14:53
Yes - I have the Global Options in RStudio set to use 32-bit R3.3.2. — user41509, Oct 30 '17 at 14:57

odbcConnect with Access database difference between R and RStudio

ODBC connection

0 Answers0