Search code examples
rodbcrodbc

ODBC works fine in MS Excel, but not in R

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?

Solution

  • 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")