JackcessDotNet 3.1.0

JackcessDotNet

Pure .NET 10 library for reading and writing Microsoft Access (.mdb / .accdb) files. No ODBC, no ACE drivers, no native dependencies, no NuGet dependencies — runs anywhere .NET 10 runs (Windows, Linux, macOS, containers).

2.0.0 targets net10.0. 1.2.x is the last net8.0 release; stay on it if you cannot move off .NET 8.

This is a C# port of the spannm/jackcess Java project (a maintained fork of the original Jackcess by James Ahlborn). The upstream Java project remains the source of truth for the Jet file format; this port tracks its behaviour and reuses its test corpus for verification.

Install

dotnet add package JackcessDotNet

Quick start

using JackcessDotNet;

// Open an existing .mdb / .accdb (auto-detects version)
using var db = Database.Open("Northwind.mdb");

foreach (var name in db.ListTables())
    Console.WriteLine(name);

var customers = db.GetTable("Customers");
foreach (var row in customers.NewCursor())
    Console.WriteLine($"{row["CustomerID"]}  {row["CompanyName"]}");

// Create a new database, define a schema, insert rows
using var fresh = Database.Create("MyData.mdb", JetVersion.Jet4);
var people = fresh.CreateTable("People", new[]
{
    new ColumnBuilder("Id",   DataType.Long).Build(),
    new ColumnBuilder("Name", DataType.Text).MaxLength(50).Build(),
}, primaryKey: "Id");
people.Insert(new Row { ["Id"] = 1, ["Name"] = "Alice" });

Opening password-protected files

// Jet RC4 (.mdb, Access 97 / 2000–2003)
using var db = Database.Open("Confidential.mdb", "passw0rd");

// .accdb encryption — Agile (Office 2010+), ECMA Standard (Office 2007),
// RC4 CryptoAPI (Office 2002–2003), or Non-Standard AES — all auto-detected
// from the EncryptionInfo header.
using var db = Database.Open("Confidential.accdb", "passw0rd");

// Inspect which scheme the codec picked (useful when triaging an unfamiliar file).
byte[] page0 = File.ReadAllBytes("Confidential.accdb").Take(4096).ToArray();
var codec = OfficeCryptCodecHandler.FromDbHeader(page0, "passw0rd");
Console.WriteLine(codec?.Scheme);   // → "Agile Encryption (Office 2010+)"

Wrong passwords throw UnauthorizedAccessException before any data page is parsed — no cryptic "page X corrupt" errors.

Composite (multi-column) primary keys

db.CreateTable("Orders", new[]
{
    new ColumnBuilder("CustomerId", DataType.Long).Build(),
    new ColumnBuilder("OrderId",    DataType.Long).Build(),
    new ColumnBuilder("Total",      DataType.Money).Build(),
}, primaryKeyColumns: new[] { "CustomerId", "OrderId" });

Up to 10 columns per composite key (Jet's hard limit). The single-column primaryKey: overload still works for the common case.

Foreign-key enforcement on insert (opt-in)

using var db = Database.Open("Northwind.mdb");
db.EnforceForeignKeys = true;                  // default: false

// MSysRelationships drives validation. Any Insert whose FK column doesn't
// match an existing parent row throws InvalidOperationException.
orders.Insert(new Row { ["CustomerID"] = "INVALID", ... });
//                                                    ^ throws "Foreign-key violation in
//                                                              relationship 'CustomersOrders'..."

Null FK values are allowed (SQL semantics). Restrict-only — no cascade.

Importing from DataTable, DataSet, or IEnumerable<T>

Skip the schema boilerplate — the importer infers columns from the source type:

using var db = DatabaseImporter.CreateFromDataTable("out.mdb", myDataTable);

// or add into an existing database
db.ImportTable(otherDataTable, tableName: "Extra", primaryKey: "Id");
db.ImportTables(myDataSet);

db.ImportTable<Customer>(customers, primaryKey: "Id");

// Append rows into a pre-existing table (instead of failing):
db.ImportTable(moreRows, options: new ImportOptions { AppendIfExists = true });

POCO mapping respects [Key], [Column("X")], [MaxLength], [NotMapped], and [DatabaseGenerated(Identity)]. Long strings (>255 chars) auto-promote to Memo; large byte arrays (>255 bytes) to OLE. Unmappable types can fall back to Memo via ImportOptions.FallbackUnmappableToString = true.

Exporting back

DataTable          dt    = db.ExportToDataTable("Customers");
DataSet            ds    = db.ExportToDataSet();
IEnumerable<Customer> cs = db.ExportToCollection<Customer>("Customers");

The IEnumerable<T> path is lazy via yield, so Take(n) stops early without materialising the whole table.

Status

³ Two paths, both checked by reading the result back through Microsoft.ACE.OLEDB.12.0: creating a file and a table from scratch, and appending into a table an Access-authored file already contains. Before 2.2.0 the first was unreadable by Access and the second garbled Latin-1 text — see the 2.2.0 entry in CHANGELOG.md for the five on-disk details involved.

⁴ Access reads through indexes, so a row absent from one does not exist as far as Access is concerned even though a page scan still returns it. Every index of the table takes an entry, each index's entry count is kept current (Access answers COUNT(*) and MAX from an index), and a full leaf splits correctly — including in a tree Access wrote, which needs the new root recorded against the right index-data block rather than the right slot; the two are ordered independently. Pages Access prefix-compressed are expanded and re-emitted in full, and a full node splits under a new level, so trees grow past two levels — all three verified by querying the result through ACE. An insert is refused only when a key is too large for three to share a page, which is what splitting a node needs, and Jet's 255-byte key limit puts that out of reach.

⁵ db.CreateIndex("People", "ByName", "Name") — up to 10 columns, ascending by default or descending via IndexColumnSpec, spliced into the table's existing definition and then filled from the rows already stored, so it answers queries immediately. Access lists it (verified through ADOX) and uses it for seeks, ORDER BY, GROUP BY and MAX. Two caveats: the definition has to still fit on one page, and a definition that outgrows it continues on another. unique: true is enforced on insert.

² A table's data pages are tracked in a usage map whose inline bitmap addresses a fixed window — as little as 512 pages (~2 MB) in Access-authored files. The window slides to follow the table, and once the table outgrows one bitmap the map is promoted to reference-style (~2.2 GB of reach, past Access's own file limit). Before 2.0.0 an append past the window threw.

¹ Agile write doesn't recompute the DataIntegrity HMAC, so files modified through this library round-trip cleanly through Database.Open(path, password) but Office Access may flag a stale integrity hash. Matches the upstream jackcess-encrypt limitation.

Type round-trip cheat sheet

Version history

See CHANGELOG.md — also shipped inside the .nupkg.

Credits

This project would not exist without the years of reverse-engineering work done by James Ahlborn (original Jackcess) and Markus Spann (maintained fork at spannm/jackcess). The Jet binary format is documented effectively only through their Java source.

License

Apache License 2.0 — same as the upstream Jackcess project.

⁷ AgileDataIntegrity computes and verifies the encryptedHmacKey / encryptedHmacValue pair of MS-OFFCRYPTO §2.3.4.14, and OfficeCryptCodecHandler exposes them for an Agile-encrypted file. It is checked against itself — tampered content, wrong key and swapped ciphertexts all fail, every Agile hash round-trips — but not against Microsoft's output: that needs an Agile file already carrying a <dataIntegrity> element to recompute, or real Access to open one written here, and neither the ACE nor the DAO engine substitutes. Which bytes the hash covers is the caller's to decide; the specification defines it over an OOXML package's encrypted stream, and an Access database has no such stream.

⁸ Ordinary rows and complex values write correctly into an existing .accdb, and the ACE engine reads both back — including a multi-value entry and an attachment appended by AddComplexValue. A Memo or OLE value is not there yet: the write completes and round-trips through this library, but ACE reads the value back as empty. The page accounting around it is now right — Access and this library agree on the table's row count, which they did not before the long-value map's row was honoured — so what remains is the chain's own content. Note that every other write path here is exercised against Jet 4 .mdb — that gap is how the encoder came to disagree with the reader about where a value lives.

⁹ Database.Create(path, JetVersion.Jet12…Jet17) writes a Jet 4 database — the header reads Standard Jet DB version 0x01 whatever the file is called. True ACE format, with its own header, system tables and page structures, is not written. It is still useful: Access and the ACE engine open a Jet 4 file regardless of extension, so a caller that creates one, fills it and hands it on gets a file that works. Reading a real .accdb is genuine ACE and unaffected.

¹⁰ Jet 4's integer types stop at Long (Int32). Its only 8-byte types are Money, Double and ShortDateTime, and none is a general 64-bit integer. BIGINT — Access's "Large Number", data type 0x13 — arrived with ACE 16 (Access 2016) and only when that option is enabled; this library does not implement it, so a column using it cannot be read either.

For an exact 64-bit value in a Jet 4 file, use DataType.Numeric (0x10): a 17-byte decimal with up to 28 digits of precision, which holds any Int64 exactly. The alternatives all lose something — Money is internally an Int64 of ten-thousandths, so it caps at ±922,337,203,685,477.5807 and carries currency semantics; Double is exact only to 2^53, and silently wrong above it.