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?

Karolyn

Answer 1

Not sure if this is the most correct way to go about this, but this worked for me.

1. Go to the spreadsheet in excel with the data imported.
2. Click on Data -> Connections
3. Double click on the query
4. Click on the Definition tab

5. Copy the Connection string and paste it into R inside the odbcDriverConnect function:

   myConn <-odbcDriverConnect("DRIVER=SQL Server;SERVER=fooServer;UID=foo_viewer;PWD=1pityDfoo!;APP=Microsoft Office 2010;WSID=foocomputername;DATABASE=DTS")

6. You can also copy the Command text and paste it into R inside the sqlQuery function:

   mydata <- sqlQuery(myConn, "SELECT DTS.dts_id, DTS.dts_no, DTS.unit_code, DTS.originator, DTS.doc_type_id, DTS.doc_date, DTS.subject, DTS.remarks, DTS.status, DTS.is_confidential, DTS.created_by, DTS.date_created, DTS.updated_by, DTS.date_updated, DTS.timestamp FROM DTS.dbo.DTS DTS")

Karolyn

Answer 2

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

Flite

Answer 3

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.

Mountfort

Answer 4

This work-around helped me:

1. Go to ODB Data Source Administrator ([Win] + ODBC...)
2. Add new DNS (doesn't really matter what type, keeping User DNS); point the source to desired DB and name the new DNS (e.g. dbAdHocForR)
3. Go back to R to define the channel (just call the DNS w/o any additional parameters): ch <- odbcConnect("SQL Server")
4. Define the SQL query using the channel: q1 <- sqlQuery(ch, "select top 1000 * from dbo.RTestTable")

Emlen

Answer 5

Try using odbcDriverConnect and a connection string.

ch <- odbcDriverConnect("Driver={Oracle in OraClient11g_home1};Dbq=leprosyDHISdb;Uid=leprosyDHISid;Pwd=leprosyDHISpw;")

You might have to check https://www.connectionstrings.com/ for your specific connection string. This is the one I used.

Eritrea

Thanks, @Phil. Unfortunately, this still doesn't seem to work. – Karolyn

Once I got the driver right, it did work. Thanks @Phil! – Karolyn