Can‘t connect to Oracle Database

[Andi]

Hello,
I am new with R and I am trying to create a connection to our Oracle Database. In VBA we use the following code, which works perfekt:

Set cnn = New ADODB.Connection
    sCnn = "DSN=DSN_NAME;" _
        & "UID=USER_NAME;" _
        & "PWD=USER_PASSWORD;" _
        & "DBQ=DBQ_NAME;" _
        & "DRIVER={Microsoft ODBC for Oracle};"
    
    With cnn
        .ConnectionString = sCnn
        .Open
    End With

In R I have tried several possibilities with ODBC and RODBC, but none works. For example:

myConn <-odbcDriverConnect("Driver={{Microsoft ODBC for Oracle};DBQ=xxx;uid=xxx;Pwd=xxx;")

Error in odbcDriverConnect("Driver={{Microsoft ODBC for Oracle};DBQ=xxx;uid=xxx;Pwd=xxx;") : 
  could not find function "odbcDriverConnect"

Or:

con <- odbc::dbConnect(odbc::odbc(), dsn=dsn_name, uid=user_name, pwd=pw)

Error: nanodbc/nanodbc.cpp:950: IM014: [Microsoft][ODBC Driver Manager] Der angegebene DSN weist eine nicht übereinstimmende Architektur von Treiber und Anwendung auf.

Can somebody help me setting up a connection to the Oracle database?

Cheers,
Andi

[stewart.ross]

First, are you using the 64-bit version of R? If you are, you need to switch to the 32-bit version of R instead as the Oracle ODBC driver you use in VBA is undoubtedly a 32-bit driver, which will not be recognised if you are running the 64-bit version of R. In an MS Windows installation on Win 7 and above, the 32-bit driver manager used to set up ODBC dsn files is odbcad32.exe in the sysWOW64 folder.

Second, the error message about the odbcDriverConnect function not being recognised suggests either that RODBC was not loaded at the time, or that (in line with what you show later from the odbc package) that if you do not wish to load the package explicitly you need to qualify your call accordingly: RODBC::odbcDriverConnect()

[Andi]

Thanks Steward - changing to the 32-bit version of R is the solution!