Connecting to Microsoft Access Database

xs2urvashisingla
xs2urvashisingla Guest Posts: 1

Hi,

I am using microsoft access DB in my R code. the access file is uploaded in the project.

I am trying to connect using library(odbc). Code below -

con<-dbConnect(odbc(),.connection_string="Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=[db name.mdb]")


The code when I run on my workspace generates an error -

nanodbc/nanodbc.cpp:1021:00000: [unixodbc][Driver Manager] Can't open lib 'Microsoft Access Driver (*.mdb, *.accdb)' : file not found

I believe the drivers are not present by default. I am using standard Domino Analytic Distribution Py3.6 R 3.6 environment.

How can I establish this connection successfully. Any suggestion would be appreciated.


Thanks,

Urvashi



Answers

  • petter.olsson
    petter.olsson Member, Moderator, Domino Posts: 78 mod

    Hi Urvashi,

    It would seem the "file not found" is key here. The Access file would need to be specified in a location that is accessible by R in this case.

    Thanks

    Petter

  • xs2urvashisingla
    xs2urvashisingla Guest Posts: 1

    Hi Petter,

    The access file is placed in the same location where the R code is placed so it should have access. Moreover the error talks about the driver lib and not the access file. So not sure of how to resolve that?


    Thanks,

    Urvashi

  • petter.olsson
    petter.olsson Member, Moderator, Domino Posts: 78 mod

    Hi Urvashi,

    You are correct, an error on my part. Try loading the rodbc driver before that code, library(rodbc), and see if that helps. If that is not working we can try to re-install the driver with apt-get to make sure it is truly present.

    Thanks

    Petter

  • xs2urvashisingla
    xs2urvashisingla Guest Posts: 1

    Hi Petter,

    Thanks for the reply. I tried both rodbc and odbc but it gives the same error. Can you please elaborate on how to re-install the driver?

    Regards,

    Urvashi

  • bjorn.robertsson
    bjorn.robertsson Member, Moderator, Domino Posts: 0 mod

    Hello Urvashi,

    In order to use the ODBC connection to MS SQL, you will need:

    From the OS, the appropriate 'odbc' package, for Ubuntu add to the Dockerfile (you can run the commands from a Terminal with sudo, if it's a one-off):

    RUN apt-get update && apt-get install -y unixodbc-dev

    From the R environment, you need the corresponding R library:

    install.packages('odbc')

    and from your code, you must enable it with:

    library(odbc)


    You can also avoid the R installation, by including it in the Dockerfile, this is useful if you use the MS SQL connection often:

    RUN R -e 'options(repos=structure(c(CRAN="http://cran.us.r-project.org"))); install.packages(c( "odbc"))'

    Remember, this means modifying your environment, and you can either modify the Default environment or make a copy for your own projects. It might require discussion internally or with the Domino Admins on how this is done.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!