ODBC works fine in MS Excel, but not in R

Question

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 1

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

Answer 2

I had the same issue recently. This is how I solved it:

1) Go to page: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html and select the relevant OS and version to you.

2) Download the package using the link under "Instant Client Package - ODBC: Additional libraries for enabling ODBC applications"

3) Extract the files under C drive as C:\instantclient_12_1 and run odbc_install.exe in it.

4) Try connecting again.