0

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.

Community
  • 1
  • 1
user41509
  • 978
  • 1
  • 10
  • 31
  • 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
  • I am running 32 bit R and 32 bit RStudio. – user41509 Oct 30 '17 at 14:50
  • 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
  • Can you try `scallopchannel<-odbcConnectAccess2007("S://Adv/Scallop Central/2015 RSA/2015 RSA Database/2015 RSA Database_9-4-2015.accdb")`? – Erik A Oct 30 '17 at 15:01
  • Yes that code works – user41509 Oct 30 '17 at 16:01

0 Answers0