Failure to connect to odbc database in R

Question

I've been trying to connect my company's DMS to R using the odbcConnect command, but get the following message:

myConn <-odbcConnect("NZSQL", uid="cejacobson", pwd="password")
Warning messages:
1: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In odbcDriverConnect("DSN=NZSQL;UID=cejacobson;PWD=password") :
  ODBC connection failed

The thing is, I'm positive the Data source name is NZSQL and my uid and password are correct as well. Any insight as to why R may not be finding my data source / driver (the driver is, by the way, specified and working).

How can I fix this?

Answer 1

I ran across this same problem when I was first trying to connect to an Oracle database. In the end what worked for me was using odbcDriverConnect and a connection string instead of odbcConnect.

myConn <-odbcDriverConnect("Driver={Oracle in OraClient11g_home1};Dbq=NZSQL;Uid=cejacobson;Pwd=password;")

You can check on https://www.connectionstrings.com/ for your specific connection string for your database. Mine happened to be this one.

Hope this helps.

Answer 2

This is IM02 error which means name of the DSN is incorrect.

GO to ODBC and check the USER/System DSN that you should be using. Once your name of DSN is correct, you might get IM014 state error which is archtecture mismatch. In that case,

The simpler solution is IN r studio - go to tools and change the version of R to 32 bit.

It should be ready to work