Read tables from a DeST model and convert to SQLite

Usage

read_dest(
  accdb,
  tables = NULL,
  sqlite = ":memory:",
  verbose = FALSE,
  drop = TRUE
)

accdb: [string] Path to the DeST model file. Usually a Microsoft Access database file with .accdb extension.
tables: [character] Vector of table names to read from the DeST model. If NULL, which is the default, all tables will be read.
sqlite: [string] Path to the SQLite database file. If :memory:, which is the default, a temporary in-memory database will be created.
verbose: [logical] Whether to print information about the conversion process. Default is FALSE.
drop: [logical] Whether to drop tables in the SQLite database if they already exist. Default is TRUE.

[DBIConnection] A SQLite database connection with all specified tables.

The function converts a Microsoft Access database (.accdb or .mdb) to SQLite using the following approach:

Driver Selection: The function tries to use ODBC drivers first:
- On Windows: Primarily uses "Microsoft Access Driver (*.mdb, *.accdb)"
- On macOS/Linux: Tries to find available drivers like "MDBTools Driver"
Fallback Mechanism: If ODBC connection fails, on non-Windows systems, the function falls back to using MDBTools command-line utilities
Table Selection:
- If tables = NULL, all tables are read except for system tables (MSys*)
- Otherwise, only the specified tables are processed
Data Transfer: For each table:
- Optionally drops existing table in target SQLite database
- Reads the entire table from Access
- Writes the data to SQLite
Schema Handling: Table structure (columns and types) is automatically preserved during the transfer process

For systems without proper ODBC drivers (especially some Linux distributions), consider installing one of the following:

Tables with the same name will be overwritten in the SQLite database.