Connecting R with MS Access Database

[Rcode]

Hello!
In monthly basis I need to extract few tables from MS Access application and I thought I can improve that process by automating it using R.
Therefore, first step is to connect R with that database. I have seen some technical steps using RODBC package online but without success. My machine is running on 64-bit windows and I am using MS Access 32 bit version. By online sources I have learnt I should create the database name and the driver in ODBC Data Source Administrator and here I am not clear as follows:
Based on my windows and MS Access given versions above do I need to have this database name and its driver created on ODBC32 or ODBC64, and by assuming this is clear, at which tab this database should exist, at User DSN or System DSN? (For clarifying I have attached two screenshots for both ODBC versions at each mentioned TABS.)
I would have appreciated any suggestion.

[craig.parylo]

Hi @Rcode, Thank you for your question.

I have little experience connecting to MS Access databases from R but I'm happy to workshop a little with you.

Using this article I was able to connect to a local copy of the Northwind database by supplying a path to the .accdb file.

library(RODBC)
library(here)
# set the path to the database
db <- file.path('C:/path/to/db.accdb')

# create a 'channel' to the database
channel <- RODBC::odbcConnectAccess2007(db)

# use the channel when gathering data - here listing available tables
RODBC::sqlTables(channel)

# or when querying a table such as the 'Customers'
df_customers <- RODBC::sqlFetch(channel = channel, sqtable = 'Customers')

Providing you can't connect using the above simple approach the article goes on to include instructions on setting up a DNS connection and recommends setting up the ODBC data source to match your version of MS Access (32 bit as you've described).

The article used a User DSN connection, though I'm not placed to tell you whether this is more appropriate than a System DSN connection.

Hopefully this helps.

[Rcode]

Hi Craig.Parylo,
First thank you for your replying and spending your time on this technical issue.
Before I posted this question I did use this article and could not make any progress .
However, recently I discovered that RODBC was giving issues because I was using Office 32 bit version and corresponding 32 bit driver.
Therefore, it worked after I uninstalled Office and reinstalled Office 64 bit version and additionally, I had to install another component which is Microsoft Access Database Engine 2016 Redistributable .
Thank you.

[craig.parylo]

You're welcome, @Rcode.
I'm pleased you've resolved the issue