Skip to content

README.md

Latest commit

 

History

History
113 lines (91 loc) · 5.22 KB

README.md

File metadata and controls

113 lines (91 loc) · 5.22 KB
Raw

Microsoft Access (.mdb / .accdb)

Reads Microsoft Access databases in pure Power Query M. No ACE OLEDB provider, no ODBC driver, no bitness matching, no admin rights, no gateway software.

Power BI does ship an Access connector, but it is a thin wrapper over the ACE OLEDB provider, and ACE is where refreshes go to die: 64-bit ACE must be installed on the gateway machine, on the Desktop the ACE and Power BI bitness must match, Click-to-Run Office installs virtualize their ACE copy so the gateway never sees it, and cloud hosts cannot install it at all. The result is the notorious The 'Microsoft.ACE.OLEDB.12.0' provider is not registered error. This reader consumes plain bytes instead, so it works anywhere Power Query runs. In the Power BI Service it refreshes without a gateway when the file itself is Service-reachable (SharePoint, OneDrive, blob storage); on an internal share you still need a gateway, just a standard one with nothing installed on it.

Usage

Paste AccessReader.Database.pq into a blank query and name the query AccessReader.Database. Power Query treats a query whose expression is a function as an invocable function.

The name avoids Access.Database on purpose. That one is a built-in engine function; a query can shadow it, but a section document cannot declare shared Access.Database at all — the module fails to compile — so the pasted reader and the one inside PQDriverless.mez would end up with different names.

let
    db  = AccessReader.Database(File.Contents("C:\data\example.accdb")),
    tbl = db{[Name = "MyTable"]}[Data]
in
    tbl

The result is a navigation table with one row per user table. The file format version and creation date are attached as table metadata (Access.Version, Access.Format, Access.CreationDate).

Options

Second argument, optional record, all keys optional:

Key Default Effect
IncludeSystemObjects false List MSys* and other system tables in the navigation table
MaxRows all rows Stop after N rows per table
Strict false Error on tolerated malformations (unsupported column types, unknown usage-map formats)

Supported

  • Jet 4 (.mdb, Access 2000 to 2003) and ACE (.accdb, Access 2007 and later).
  • All common column types: Byte, Integer, Long Integer, Currency, Single, Double, Date/Time, Text, Memo / Long Text, Yes/No, Replication ID (GUID), Decimal, Binary, OLE Object, Big Integer.
  • Fixed and variable-length columns, null bitmaps, deleted-row and moved-row (overflow) bookkeeping, multi-page table definitions, rows written before columns were added to the table.
  • Unicode-compressed text (the Jet 4 one-byte-per-character scheme).
  • Memo and OLE values in LVAL storage: inline, single-page, and multi-page chains.

Not supported

  • Encrypted or password-protected databases. Both the Jet database key and the Access 2007+ page encryption are detected and produce a clear error. Remove the password in Access (Decrypt Database) first.
  • Jet 3 (.mdb, Access 97 and earlier, 2 KB pages). Clear error; convert the file in Access.
  • Linked tables and queries. They hold no local data and are not listed.
  • Complex / multi-value and attachment columns decode to the raw long-integer key of the hidden child table that stores their values.
  • Indexes are ignored (not needed for reading).

Type mapping

Access M
Yes/No logical (stored in the null bitmap; never null)
Byte, Integer, Long Integer, Big Integer Int64.Type
Currency Currency.Type
Single, Double, Decimal number
Date/Time datetime (OLE Automation date, epoch 1899-12-30)
Text, Memo, GUID text
Binary, OLE Object binary

Integers beyond 2^53 lose precision (M numbers are doubles). The same applies to Currency beyond 2^53 / 10000 and to wide Decimal values.

How it works

An Access file is a sequence of 4 KB pages. Page 0 holds the signature, format version, and a header region masked with a fixed RC4 keystream (a format constant, not security; unmasking it yields the database key used for the encryption check and the creation date). Page 2 is always the table definition of MSysObjects, the system catalog, which lists every object with its name, type, and the page of its table definition. Each table definition carries the column list (type, fixed offset or variable index, length) and a usage map of the pages the table owns. Data pages hold a slot directory; each row is a stored column count, fixed columns, variable columns, a variable-offset table, and a null bitmap. Long values live on LVAL pages addressed by 4-byte row pointers. The reader follows exactly this chain and nothing else.

The format is not publicly documented by Microsoft. The layout follows the MDB Tools project's format documentation, cross-checked against Jackcess (both Apache-2.0, read for understanding, no code copied), and is validated against files written by Jackcess; see test/expected.md.

Testing

Fixtures in test/ are synthetic, generated by test/MakeFixtures.java (Jackcess) and test/make_encrypted.py. What each fixture proves is documented in test/expected.md.