Importing and renaming multiple tables from .accdb database-CodePudding

I have a large Access database (ms access 2016) that I am trying to analyze through R (2021.09.0 , 32 bit)

I have managed to access the database and get the desired output for a single dataset, but now I would like to set up a loop to plot every one of the 171 datasets. The datasets have varying numbers of rows and columns.

##View available databases##
odbcDataSources()

##Connect to database##
ch<-odbcConnect("mydsn1")

##View Availavble tables##
sqlTables(ch)

##Data transferred to R##
tbls <- sqlTables(ch)

##remove unwanted tables##
tbls <- tbls[-c(1:79),]

##import data from a single table##
TABLE1<-sqlFetch(ch, 'TABLE_NAME')
str(TABLE1)

##Import data from all data sets##
...

I cant for the life of me figure out how to import all the datasets without doing each one manually. Ideally, I could get each table imported to view, and retain their Table_name

I will likely have more questions I try to loop the plotting, but once I resolved this first step, I will attempt it on my own.

CodePudding user response：

You could collect your imported tables in a list (and move on from there):

## tbls being your vector of table *names*:

all_tables <- tbls |>
    lapply(\(table_name){
        sqlFetch(ch, table_name)
    })

## name your table list for convenient extraction of single dataframes:
names(all_tables) <- tbls