[R-SIG-Mac] Using Microsoft Access from a Macintosh using R

[Marc Schwartz]

Hi,

You have identified the key components that you will need, which are the RODBC package:

  https://cran.r-project.org/web/packages/RODBC/index.html

and the Actual Technologies ODBC driver pack for MS Access:

  https://www.actualtech.com/product_access.php

The latter has some basic installation instructions for that product on the above page, and Actual offers a limited function evaluation option, if you want to try before you buy. Actual also has their own tech support available. The key is using their tools to set up the correct DSN specification for the database that you want to retrieve data from within R.

I used to use the Actual ODBC driver pack for Oracle on my Mac, and it worked quite well, albeit, it has now been a few years since I needed that functionality.

For the RODBC package, it can be installed from within an R session using:

  install.packages("RODBC")

Note that Prof. Ripley has spent a significant amount of time over the years creating a vignette for the RODBC package:

  https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf

in addition to the package manual, and I would urge you to review the vignette for helpful information.

That should get you started.

Note also that there is a specific e-mail list for R and databases:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

however the traffic on that list has been essentially non-existent for some time, and since this is a Mac specific issue, feel free to continue this thread, or post subsequent questions, here.

Regards,

Marc Schwartz



On April 26, 2023 at 6:50:10 AM, list_email--- via R-SIG-Mac (r-sig-mac using r-project.org (mailto:r-sig-mac using r-project.org)) wrote:

> How can I make a Mac get data from a Microsoft Access database using R?
>
> I’m sorry if this has been asked a million times before but the list archives don’t seem to be searchable and the Mac FAQ doesn’t mention this.
>
> Also, I’m asking for a colleague who doesn’t like messing around with a lot of technical computer stuff and I do not have R on my own system. So please be patient.
>
> It seems that there is a package called RODBC which needs to be installed. Is that done using R’s package manager?
>
> And it seems that there is a driver from Actual Technologies with a Mac installer package.
>
> Assuming that these two steps are taken, is there anything else that needs to be done? Can R then access the Access database directly or is there an intermediate step such as an Excel file? Is there a very short MWE
> that demonstrates accessing a public Microsoft Access database using this method?
>
> My colleague is thinking of buying a Windows system just to solve this problem!
>
> Many thanks,
>
> Jerry
>
> _______________________________________________
> R-SIG-Mac mailing list
> R-SIG-Mac using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-mac