This project is read-only.

Cannot access RODBC

Mar 5, 2013 at 6:27 PM
I am trying to use RODBC under RDotNet in a VisualBasic.NET application. RDotNet works fine, but I am unable to use RODBC (to access a MS ACCESS database). I have tried lots of different solutions, but I still cannot get RDotNet to recognize the RODBC package.

First a little background:

I am using Microsoft Visual Studio 2008 and Microsoft .NET Framework Version 3.5 SP1.

I downloaded and installed "R.NET 1.3 (.NET Framework 3.5)"

Under Project Properties->Compile->Advanced Compile Options...->
Target CPU: x86
Target Framework: .NET Framework 3.5

Here is the code from my latest attempt:
(note: I used require (RODBC) instead of library(RODBC) to show the availability result, but they both fail):

Imports System
Imports System.Runtime.InteropServices
Imports RDotNet

Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Const Username As String = "MyUsername" ' Actual User name was here

    Const R_HOME As String = "C:\Program Files\R\R-2.15.2"
    Const R_LIBS As String = "C:\Program Files\R\R-2.15.2\library"
    Const R_LIBS_USER As String = "C:\Users\" & Username & "\Documents\R\win-library\2.15"
    'Const PATH As String = "C:\Program Files\R\R-2.15.2\bin"
    'Const R_DLL_PATH As String = R_HOME & "\bin"

    Dim re As REngine
    Dim rResponse As CharacterMatrix
    Dim message As String

    System.Environment.SetEnvironmentVariable("PATH", System.Environment.GetEnvironmentVariable("PATH") & ";" & _
        R_HOME & ";" & R_LIBS & ";" & R_LIBS_USER)
    System.Environment.SetEnvironmentVariable("R_HOME", R_HOME)
    System.Environment.SetEnvironmentVariable("R_LIBS", R_LIBS)
    System.Environment.SetEnvironmentVariable("R_LIBS_USER", R_LIBS_USER)

    Console.WriteLine("PATH: " & System.Environment.GetEnvironmentVariable("PATH"))
    Console.WriteLine("R_HOME: " & System.Environment.GetEnvironmentVariable("R_HOME"))
    Console.WriteLine("R_LIBS: " & System.Environment.GetEnvironmentVariable("R_LIBS"))
    Console.WriteLine("R_LIBS_USER: " & System.Environment.GetEnvironmentVariable("R_LIBS_USER"))
    'Console.WriteLine("R_DLL_PATH: " & R_DLL_PATH)

    REngine.SetDllDirectory("C:\Program Files\R\R-2.15.2\bin\i386")

    re = REngine.CreateInstance("RDotNet")

    For Each s As String In re.EagerEvaluate(".libPaths()").AsCharacter
        Console.WriteLine(".libPaths(): " & s)

    re.EagerEvaluate("l = .packages(all.available=TRUE)")
    rResponse = re.GetSymbol("l").AsCharacterMatrix
    message = rResponse.Item(0, 0)
    For i As Integer = 1 To rResponse.RowCount - 1
        message = message & ", " & rResponse.Item(i, 0)
    Console.WriteLine(".packages(all.available=TRUE): " & message)

    re.EagerEvaluate("l = library()")
    rResponse = re.GetSymbol("l").AsCharacterMatrix
    message = rResponse.Item(0, 0)
    For i As Integer = 1 To rResponse.RowCount - 1
        message = message & ", " & rResponse.Item(i, 0)
    Console.WriteLine("library(): " & message)

    re.EagerEvaluate("r = require(RODBC,quietly=TRUE)")
    rResponse = re.GetSymbol("r").AsCharacterMatrix
    Console.WriteLine("require(RODBC,quietly=TRUE): " & rResponse.Item(0, 0))

End Sub
End Class

Console Output:

PATH: C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;c:\Program Files (x86)\Microsoft SQL Server\90

\Tools\binn\;C:\Program Files (x86)\CyberLink\Power2Go;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program

Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Windows\System32

\WindowsPowerShell\v1.0\;C:\Program Files (x86)\QuickTime\QTSystem\;C:\Program Files\R\R-2.15.2;C:\Program Files\R\R-2.15.2


R_HOME: C:\Program Files\R\R-2.15.2

R_LIBS: C:\Program Files\R\R-2.15.2\library

R_LIBS_USER: C:\Users\MyUsername\Documents\R\win-library\2.15

.libPaths(): C:/Program Files/R/R-2.15.2/library
.libPaths(): C:/Users/MyUsername/Documents/R/win-library/2.15

.packages(all.available=TRUE): base, boot, class, cluster, codetools, compiler, datasets, foreign, graphics, grDevices,

grid, KernSmooth, lattice, MASS, Matrix, methods, mgcv, nlme, nnet, parallel, RODBC, rpart, spatial, splines, stats, stats4,

survival, tcltk, tools, utils, DBI, ODB, rJava, RJDBC, xlsx, xlsxjars

library(): NULL, c("base", "boot", "class", "cluster", "codetools", "compiler", "datasets", "foreign", "graphics",

"grDevices", "grid", "KernSmooth", "lattice", "MASS", "Matrix", "methods", "mgcv", "nlme", "nnet", "parallel", "RODBC",

"rpart", "spatial", "splines", "stats", "stats4", "survival", "tcltk", "tools", "utils", "DBI", "ODB", "rJava", "RJDBC",

"RODBC", "xlsx", "xlsxjars", "C:/Program Files/R/R-2.15.2/library", "C:/Program Files/R/R-2.15.2/library", Yadda, yadda, yadda...

"C:/Users/MyUsername/Documents/R/win-library/2.15", "The R Base Package", "Bootstrap Functions (originally by Angelo Canty for S)", "Functions for Classification", "Cluster Analysis Extended Rousseeuw et al.", "Code Analysis Tools for R",
"The R Compiler Package", "The R Datasets Package", "Read Data Stored by Minitab, S, SAS, SPSS, Stata, Systat, dBase,\n...",

more yadda, yadda, yadda

Parallel computation in R", "ODBC Database Access", "Recursive Partitioning", "Functions for Kriging and Point Pattern

Analysis", "Regression Spline Functions and Classes", "The R Stats Package", "Statistical Functions using S4 Classes",

"Survival analysis, including penalised likelihood.", "Tcl/Tk Interface", "Tools for Package Development", "The R UtilsPackage", "R Database Interface",
"Open Document Databases (.odb) management", "Low-level R to Java interface", "Provides access to databases through the JDBC interface", "ODBC Database Access", "Read, write, format Excel 2007 and Excel 97/2000/XP/2003 files", "Package required jars for the xlsx package"), NULL

require(RODBC,quietly=TRUE): FALSE
Mar 9, 2013 at 10:59 PM

Unfamiliar with RODBC, but there is no reason why this would not work if it works in the R console (does it?).
If there is a difference you could get more information by using quietly=FALSE which is the default anyway and capture the console output/std error with the info from the RODBC failed load.
Mar 12, 2013 at 9:37 PM
RODBC works fine using the Console in R i386 2.15.2. The RODBC package is shown when I list packages using R.NET. I am developing in VB.Net and there are no messages (other than the ones I display) displayed by R that I can see in the Output window. Is there a way I can view messages generated by R? I don't know how to redirect R messages so I may view them. I am using R.NET 1.3 because the version of VB.Net I am using only supports the 3.5 .NET framework and nothing later. Could it be possible that R.NET version 1.3 does not work properly using R 2.15.2 packages or some other version problem?