Work with Microsoft Access Files

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.

mdbr

)

The goal of mdbr is to easily access the open source MDB Tools written by Brian Bruns. The MDB Tools C library is now bundled with the package — no external installation is required. This package reads proprietary Microsoft Access files directly and returns standard R data frames.

Installation

You can install the release version of mdbr from CRAN.

install.packages("mdbr")

The development version can be installed from GitHub.

# install.packages("remotes")
remotes::install_github("k5cents/mdbr")

Example

library(mdbr)

The package bundles the Northwind Access database, including related tables, foreign keys, and non-ASCII names. Find it with mdb_example().

The tables in a database can be listed with mdb_tables().

mdb_tables(ex <- mdb_example())
#> [1] "Order Details" "Orders"        "Products"      "Shippers"      "Categories"    "Customers"    
#> [7] "Employees"     "Suppliers"     "Umsätze"

These tables can be exported as a delimited string or file.

string <- export_mdb(ex, "Shippers", output = TRUE, delim = "|", quote = "'")
cat(string, sep = "\n")
#> ShipperID|CompanyName|Phone
#> '1'|'Speedy Express'|'(503) 555-9831'
#> '2'|'United Package'|'(503) 555-3199'
#> '3'|'Federal Shipping'|'(503) 555-9931'

Tables are read directly into R as a tibble with automatic type coercion.

read_mdb(ex, "Shippers")
#> # A tibble: 3 × 3
#>   ShipperID CompanyName      Phone         
#>       <int> <chr>            <chr>         
#> 1         1 Speedy Express   (503) 555-9831
#> 2         2 United Package   (503) 555-3199
#> 3         3 Federal Shipping (503) 555-9931

The DDL for a table can be retrieved with mdb_schema(mode = "ddl").

mdb_schema(ex, "Shippers", mode = "ddl")
#> [Shippers]
#> -- That file uses encoding UTF-8
#> 
#> CREATE TABLE [Shippers]
#>  (
#>  [ShipperID]         Long Integer,
#>  [CompanyName]           Text (40) NOT NULL,
#>  [Phone]         Text (24)
#> );

Column types are returned as a readr col spec (requires the readr package). Use condense = TRUE to collapse columns sharing a type.

mdb_schema(ex, "Shippers")
#> cols(
#>   ShipperID = col_integer(),
#>   CompanyName = col_character(),
#>   Phone = col_character()
#> )