ODBC works fine in MS Excel, but not in R

r odbc rodbc
Tom picture Tom · Jun 5, 2014 · Viewed 7k times · Source

I've set up my ODBC driver so that MS Excel can import the data into a spreadsheet just fine.

However, when I try to establish the connection with R, using

ch <- odbcConnect(leprosyDHISdb, uid = leprosyDHISid, pwd = leprosyDHISpw)

Then I get the error

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

What could make odbc fail for R, but not for Excel?

Answer

Daniel Gardiner picture Daniel Gardiner · Jun 23, 2014

Check if you are using a 32/64bit version of R, also check if your ODBC connection is 32/64bit.

To setup a 32bit DSN using windows navigate to the following folder C:\Windows\SysWOW64

To setup a 64bit DSN using windows navigate to the following folder C:\Windows\System32

EDIT: The following blog is an excellent guide you might find useful

http://sandymuspratt.blogspot.co.uk/2013/01/getting-access-data-into-r.html

Related questions

closing unused RODBC handle

I have been receiving a Warning Message: `historicalHourly <- importHistoricalHourly(startDatePast,endDatePast,Markets,location) [1] "Importing Hourly Data" [1] "Flag - Moving from importHistoricalHourly to CleaningUpHourly" [1] "Flag - Moving to importHistoricalDaily from CleaningUpHourly"Warning messages: 1: closing unused RODBC handle 41 2: closing unused …

Read More
First argument is not an open RODBC channel

I am connecting to an Oracle database via DNS (set up the connection on the tnsnames.ora with the name "database"). I am able to succesfully run the following code: con <- DBI::dbConnect(odbc::odbc(), "database", UID = "user", …

Read More
Unable to append to SQL Server table using sqlSave in R

I am trying to update a SQL table using sqlSave function of RODBC package in R. Data is present in a data frame. When I try to run the command: sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T) …

Read More