Convert MS Access .mdb database files to alternative formats for work under Linux?

Question

For a long time, I am struggling with .mdb databases I receive for my work under Linux (especially with QGIS). Even from within Access on Windows, I found no convenient way to export the whole database, but only an export to separate .csv files, which is tedious and stupid.

There are online converters available, but they all have a limit in upload size around 50MB, so most of my databases do not fit.

Other proposals were to connect via ODBC or convert via JAVA libraries, but both ways were too complicated or failed for me.

Answer 1

I wrote a script in R for converting MS Access .mdb files to SQLite. It is using the Hmisc library for reading and RSqlite package for writing to a .sqlite file, which performs well in QGIS under Linux.

library(Hmisc) #for "mdb.get"
library(RSQLite) # for export to sqlite
library(tidyverse) # for "mutate_if"

# Path to .mdb:
mdb_path <-"/xxx/xxx/xxx/xxx/xxx/xxx/xyz.mdb" # <- Adjust path to file here

# create variables for path, filename etc. for export
dest_path <- dirname(mdb_path) #dirname from base; Change, if result should go to another directory than the source
dest_name <- tools::file_path_sans_ext(basename(mdb_path)) # file name without extension. Change if result name should be different

## load mdb
# create list of mdb-tables
lst.MDB <- list()
# populate list with data frames from mdb-tables
lst.MDB <- mdb.get(mdb_path,allow="_") # allow="_" preventing transformation from underscore to point
# transform factors to character with mutate_if
lst.MDB <- lapply(lst.MDB ,function(x) mutate_if(x,is.factor, as.character))

# create list of table names from the list of data frames
lst.MDB.names <- as.list(names(lst.MDB))
#create connection to new sqlite database
db = dbConnect(SQLite(), dbname= paste(dest_path,paste(dest_name, ".sqlite",sep=""),sep="/"))

# loop over table names
for(i in lst.MDB.names){
  dbWriteTable( #from RSqlite : https://rdrr.io/cran/RSQLite/man/dbWriteTable.html
    db, # connection
    i, # table name
    lst.MDB[[i]], # data frame form list of data frames
    field.types = NULL,
    overwrite = TRUE,
    append = FALSE,
    header = TRUE,
    colClasses = NA,
    row.names = FALSE,
    nrows = 50,
    sep = ",",
    eol = "\n",
    skip = 0,
    temporary = FALSE
  )                                            
}

#UPDATE: alternatively writing to GeoPackage using library sf

#write data tables to gpkg: https://gis.stackexchange.com/a/388760/8202
library(sf)
for(i in lst.MDB.names){
  st_write(lst.MDB[[i]],
           paste(dest_path,paste(dest_name, ".gpkg",sep=""),sep="/"),
           layer = i)                                         
}
#Done