Package {mdbr}

Title:	Work with Microsoft Access Files
Version:	0.3.0
Description:	Work with Microsoft Access '.mdb' and '.accdb' files using the open source 'MDB Tools' library https://github.com/mdbtools/mdbtools/. The library is compiled and bundled with the package, so no external installation is required. Provides high-level helpers for reading tables, exporting to CSV or JSON, inspecting table definitions, and running SQL queries. Also exposes a full read-only 'DBI' interface for use with standard database workflows.
License:	GPL-3 | LGPL-2
URL:	https://k5cents.github.io/mdbr/, https://github.com/k5cents/mdbr, https://github.com/mdbtools/mdbtools/
BugReports:	https://github.com/k5cents/mdbr/issues
Imports:	DBI, lifecycle, methods, tibble, utils, jsonlite
Suggests:	readr, testthat (≥ 3.0.0)
Encoding:	UTF-8
NeedsCompilation:	yes
SystemRequirements:	GNU make
Config/testthat/edition:	3
Config/roxygen2/version:	8.0.0
Packaged:	2026-05-22 21:25:54 UTC; kiernan
Author:	Kiernan Nicholls [aut, cre, cph], Bruno Tremblay [ctb] (DBI interface and bundled mdbtools source)
Maintainer:	Kiernan Nicholls <k5cents@gmail.com>
Repository:	CRAN
Date/Publication:	2026-05-23 11:00:07 UTC

Export an Access database table as a text file

Description

Convert the data of a table into a delimited text string. Save the string as a character vector or write it to a text file. This direct conversion makes it easy to read tables into R or a spreadsheet.

Usage

export_mdb(
  file,
  table,
  output = TRUE,
  delim = ",",
  quote = "\"",
  quote_escape = "double",
  col_names = TRUE,
  eol = "\n",
  date_format = "%Y-%m-%d %H:%M:%S"
)

Arguments

Value

Character string, invisible if path to file.

Examples

## Not run: 
export_mdb(mdb_example(), "Airlines", output = TRUE)

## End(Not run)

Create an mdbr DBI Driver

Description

mdb() is the canonical DBI-style constructor for connecting to Microsoft Access '.mdb' and '.accdb' files.

Usage

mdb()

Value

A DBI driver for '.mdb' and '.accdb' files.

Examples

db <- mdb_example()
conn <- DBI::dbConnect(mdb(), dbname = db)
DBI::dbListTables(conn)
DBI::dbDisconnect(conn)

Export Table As List Columns (mdb-array mimic)

Description

mdb-array(1) emits C source; this wrapper returns a named R list of columns while keeping equivalent database/table inputs.

Usage

mdb_array(path, table, columns = NULL, n = -1L)

Arguments

Value

Named list, one entry per selected column.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  arr <- mdb_array(db, "Products", columns = c("ProductID", "ProductName"), n = 2)
  str(arr)
}

Count Rows In Table

Description

mdb-count is a utility program distributed with MDB Tools. It outputs the number of rows in a table.

Usage

mdb_count(path, table = NULL, where = NULL, version = FALSE)

Arguments

Value

Integer row count or version string.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  mdb_count(db, "Orders")
}

Get path to mdbr example

Description

mdbr comes bundled with a sample file from the nycflights13 package in its inst/extdata directory. This function make it easy to access.

Usage

mdb_example(path = "nycflights13.mdb")

Arguments

Value

A character string with the full path to the bundled example file.

Examples

mdb_example()

Export Data In An MDB Table To CSV Or INSERT SQL

Description

mdb-export is a utility program distributed with MDB Tools. It produces CSV output for the given table. Such output is suitable for importation into databases or spreadsheets.

Usage

mdb_export(
  path,
  table,
  no_header = FALSE,
  delimiter = ",",
  row_delimiter = "\n",
  no_quote = FALSE,
  quote = "\"",
  escape = NULL,
  escape_invisible = FALSE,
  date_format = "%Y-%m-%d",
  datetime_format = "%Y-%m-%d %H:%M:%S",
  null = "",
  bin = c("strip", "raw", "octal", "hex"),
  boolean_words = FALSE,
  insert = NULL,
  namespace = NULL,
  batch_size = 1L,
  n = -1L
)

Arguments

Details

Used with insert, it outputs backend-specific SQL INSERT statements. Most formatting options also apply in insert mode.

Value

Character scalar containing CSV or SQL INSERT text.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  cat(mdb_export(db, "Products", n = 2))
}

MDB Header Summary (mdb-header mimic)

Description

mdb-header(1) writes C files; this wrapper returns a structured summary.

Usage

mdb_header(path)

Arguments

Value

Named list with version, table names and query names.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  mdb_header(db)
}

Hexdump MDB File (mdb-hexdump mimic)

Description

Hexdump MDB File (mdb-hexdump mimic)

Usage

mdb_hexdump(path, pagenumber = NULL, page_size = 4096L, n = 256L)

Arguments

Value

Single hexadecimal string.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  mdb_hexdump(db, n = 16)
}

Import CSV Into MDB (mdb-import mimic)

Description

mdb-import(1) writes to MDB files. This package is read-only, so this function validates CLI-like options and then errors.

Usage

mdb_import(path, table, csvfile, header_lines = 0L, delimiter = ",")

Arguments

Value

No return; always errors in read-only mode.

Export Data In An MDB Table To JSON

Description

mdb-json is a utility program distributed with MDB Tools. It produces JSON output for the given table. Such output is suitable for parsing in a variety of languages.

Usage

mdb_json(
  path,
  table,
  date_format = "%Y-%m-%d",
  time_format = "%Y-%m-%d %H:%M:%S",
  no_unprintable = FALSE,
  n = -1L
)

Arguments

Value

JSON string.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db) && requireNamespace("jsonlite", quietly = TRUE)) {
  mdb_json(db, "Products", n = 2)
}

Get Properties List From MDB Database

Description

mdb-prop retrieves properties for one or more objects in an MDB database. name is the name of the table, query, or other object. propcol is the name of the MSysObjects column containing properties and defaults to LvProp.

Usage

mdb_prop(
  path,
  name = NULL,
  propcol = "LvProp",
  version = FALSE,
  as_list = TRUE
)

Arguments

Value

A named list with one entry per element of name. Each entry is itself a named list of named character vectors, one per property block (e.g. "(none)" for table-level properties, or a column/field name). Access individual values with p[["Orders"]][["(none)"]]["Description"].

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  p <- mdb_prop(db, "Orders")
  p[["Orders"]][["(none)"]]["Description"]
  p2 <- mdb_prop(db, c("Orders", "Orders Qry"))
}

List Or Retrieve Queries In An MDB Database

Description

mdb-queries is a utility program distributed with MDB Tools. Without query, it lists the names of all saved queries in the database. With query, it returns the SQL text of the named query or queries.

Usage

mdb_queries(
  path,
  query = NULL,
  list = TRUE,
  newline = FALSE,
  delimiter = " ",
  as_text = FALSE,
  as_list = TRUE
)

Arguments

Value

When query is NULL: a character vector of query names (or a collapsed string when as_text = TRUE). When query is supplied and as_list = TRUE: a named mdblist of SQL text strings, one per query. With a single query and as_list = FALSE: a character scalar.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  mdb_queries(db)
  mdb_queries(db, "Orders Qry")
}

Generate Schema DDL or Column Types

Description

mdb-schema is a utility program distributed with MDB Tools. It produces DDL (data definition language) output for the given database, which can be passed to another database to recreate the Access table structure. With mode = "legacy" (the default), it returns a readr col spec for the table instead.

Usage

mdb_schema(
  path,
  table = NULL,
  mode = c("legacy", "ddl"),
  condense = FALSE,
  namespace = NULL,
  backend = c("access", "sybase", "oracle", "postgres", "mysql", "sqlite"),
  drop_table = FALSE,
  not_null = TRUE,
  default_values = FALSE,
  not_empty = FALSE,
  comments = TRUE,
  indexes = TRUE,
  relations = TRUE,
  as_list = TRUE
)

Arguments

Value

When mode = "legacy", a readr::cols() specification (optionally condensed via readr::cols_condense()). Requires the readr package. When mode = "ddl" and as_list = TRUE, a named mdblist of table-level DDL text.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  mdb_schema(db, table = "Products")
  mdb_schema(db, table = "Products", mode = "ddl")
}

SQL Interface To MDB Tools

Description

mdb-sql is a utility program distributed with MDB Tools. It allows querying of an MDB database using a limited SQL subset language. The supported SQL is intentionally small: single-table queries, no aggregates, and limited WHERE support.

Usage

mdb_sql(
  path,
  statement = NULL,
  no_header = FALSE,
  no_footer = FALSE,
  no_pretty_print = FALSE,
  delimiter = "\t",
  input = NULL,
  output = NULL,
  as_text = FALSE
)

Arguments

Details

In addition to single statements, this wrapper accepts input files similar to ⁠mdb-sql -i file⁠, strips go batch terminators, and executes the script one statement at a time.

Value

data.frame by default, or character scalar in text mode.

Examples

db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  mdb_sql(db, "SELECT [ProductID], [ProductName] FROM [Products] LIMIT 3;")
}

List tables in a Microsoft Access database

Description

mdb-tables is a utility program distributed with MDB Tools. It outputs the names of all user tables (or other object types) in an MDB database file.

Usage

mdb_tables(
  file,
  system = FALSE,
  type = c("table", "query", "systable", "any", "all", "form", "macro", "report",
    "linkedtable", "module", "relationship", "dbprop"),
  show_type = FALSE
)

Arguments

Value

A character vector of object names.

Examples

db <- mdb_example()
mdb_tables(db)
mdb_tables(db, type = "query")

Return MDB File Format Or MDB Tools Version

Description

mdb-ver will return a single line of output corresponding to the program that produced the file: JET3 (for files produced by Access 97), JET4 (Access 2000, XP and 2003), ACE12 (Access 2007), ACE14 (Access 2010), ACE15 (Access 2013), or ACE16 (Access 2016).

Usage

mdb_ver(path = NULL, version = FALSE)

Arguments

Value

Single character string with file format or mdbtools version.

Examples

mdb_ver()
db <- mdbr:::.mdb_example_nwind_path()
if (nzchar(db)) {
  mdb_ver(db)
}

Print Method For mdblist

Description

Pretty printer for multi-object text outputs returned by selected ⁠mdb_*⁠ helpers when as_list = TRUE (default).

Usage

## S3 method for class 'mdblist'
print(x, ...)

Arguments

Value

The input object, invisibly.

Read a table as data frame

Description

Reads a table directly from a Microsoft Access database using the bundled mdbtools C library. Column types are inferred from the MDB schema: integer, double, logical, POSIXct for DateTime columns, and character otherwise.

Usage

read_mdb(file, table, col_names = TRUE, col_types = NULL, ...)

Arguments

Value

A tibble.

Examples

## Not run: 
read_mdb(mdb_example(), "Airlines")

## End(Not run)