Tidy or other clever methods to importing .accdb tables into R

Question

I am attempting to import all of the tables within a relational MS Access database (.accdb) into R as separate data.frames. I have previously relied upon dbplyr and RMariaDB when accessing relational SQL databases stored online and I was under initial impressions that I could appropriate some of the methods to this .accdb, but I am becoming persuaded I was mistaken.

I have accomplished what I wanted to do with this code:

#### Libraries =================================================================
library(RODBC)
library(dplyr)
library(dbplyr)

#### Import database ===========================================================
# Define connection strings
dbq_string <- paste0("DBQ=", getwd(),"my_database.accdb")
driver_string <- "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
db_connect_string <- paste0(driver_string, dbq_string)

# Create .accdb connection
con <- odbcDriverConnect(db_connect_string)

#### Extract tables to data.frames
table1 <- sqlFetch(con, "tbltable1")
table2 <- sqlFetch(con, "tbltable2")
table3 <- sqlFetch(con, "tbltable3")

I am seeking advice on two topics here: 1.) Is there a tidyverse way of going about this? I've previously used code like:

table1 <- tbl(con, "tbltable1")
Table1 <- table1 %>% select(my_col) %>% collect()

is this not possible grammar for working through Access?

2.) Can my code where I am extracting tables to data.frames be refined by an apply-family function? I previously experimented with functions to get all of the table names (table_names) and pass it as an argument to an apply function to assign each table. I was under the impression that something like lapply(table1, sqlFetch, con) would do it, but I receive an error indicating improper table connection.

I don't have an Access database to test on, but dbplyr includes a `simulate_access` function which suggests you should be able to use the approach `table = tbl(con, "tablename")`. What happens when you try it this way? — Simon.S.A., Aug 24 '22 at 21:27

score 0 · Answer 1 · answered Aug 28 '22 at 21:29

Based on this answer, you need to use the DBI package to connect to a database in order to then use dbplyr. You may also find this question helpful.

Once you have successfully connected R and Access, then yes I would suggest:

table1 <- tbl(con, "tbltable1")
Table1 <- table1 %>% select(my_col) %>% collect()

As a good tidyverse way of approaching reading all the tables into R.

If you want to do this with lapply, then the key thing is probably to name the extra arguments.

table_names = c("table1", "table2", "table3")

all_tables = lapply(table_names, sqlFetch, channel = con)

Will likely work. But omitting the channel = may fail (because channel is an argument for sqlFetch not for lapply).

Doing this in a tidyverse way, perhaps something like:

read_tbl = function(con, tbl_name){
  tbl(con, tbl_name) %>% collect()
}

all_tables = lapply(table_names, read_tbl, con = con)

Tidy or other clever methods to importing .accdb tables into R

1 Answers1