Back to the README · Reading guide · Operations guide
Start with the create-and-insert example, then use the recipes below for existing databases, constraints, schema changes and relationships. Examples use types from JetDatabaseWriter, JetDatabaseWriter.Models and JetDatabaseWriter.Enums.
Keep other writers away from the file while your writer is open. For grouped changes and failure handling, see transactions and concurrent access.
Supports Jet3, Jet4, and ACE formats —
.mdb(Access 97+) or.accdb.Jet3 stores text and object names in the database’s code page. New Jet3 files use Windows-1252, as Access 97 does. .NET would write a character outside the code page as its closest match or
?(ŁódźasLódz,中文as??), so the writer refuses it before writing anything: a name throwsArgumentException, and a Text or Memo value in an insert or update throwsJetLimitationException. Jet4 and ACE store text as UTF-16; leading U+FEFF values have the native encoding restrictions below.
Ordinary Unicode-compressed Jet4/ACE Text beginning with U+FEFF is writable within the declared width and 6 through 255 UTF-16 code units. After the marker, it requires either at least five nonzero Latin-1 characters, or a contiguous nonsurrogate wide-character prefix with nonzero low bytes followed by at least four nonzero Latin-1 characters. Other leading-marker shapes refuse before mutation. This preserves the native compression marker rather than silently losing it. The native storage evidence distinguishes retained byte controls, tested engines and remaining native confirmation.
Ordinary Jet4/ACE Memo text beginning with U+FEFF is writable at 6 through 2048 characters when the column enables UnicodeCompression and every following character is U+0001 through U+00FF. Other leading-marker Memo values of at most 2048 characters refuse before allocation; longer values use the existing plain UTF-16 chain representation. See the native storage evidence for the encoding and remaining cases.
Path-based CreateDatabaseAsync initializes and flushes an adjacent private staging file before publishing the complete database. Publication refuses an existing destination and retains the staging file’s private-file permissions. A failure reopening the published file leaves the completed database in place; a crash before publication may leave an unused staging file. Caller-supplied streams keep their separate in-process failure contract.
Strings supplied to Binary columns use the database’s code page on every format. A character that cannot be represented exactly throws EncoderFallbackException before that row is written; supply a byte[] to store arbitrary bytes.
Ordinary Text and Binary values must fit their declared widths. An oversized value throws JetLimitationException with ValueTooLarge; the writer does not truncate it. Unicode compression does not increase a Text column’s character allowance. A refused insert, update or batch retains the statement’s original bytes, indexes and identities under the transaction contract.
The README’s schema support covers the operations documented here: local and linked-table creation, rename and deletion; column add/rename/drop; index create/rename/drop on populated tables; supported text-collation choices; defaults, validation and empty-string constraints; and primary/foreign keys with referential integrity and cascading updates/deletes. Format-specific column types and supported expression and collation families follow their documented limits. Mutations preserve surviving identities and metadata through their supported paths and refuse schemas whose required storage or indexes cannot be maintained.
Preservation checks include native properties, query definitions, complex items, permissions and inert application objects. Coverage of every native property layout, application dependency, replication variant and unknown system object remains open in the compatibility matrix and schema-preservation plan. The schema check does not promise Access application execution or UI Name AutoCorrect behavior.
await using var writer = await AccessWriter.OpenAsync("database.mdb");
await writer.CreateTableAsync("Contacts", new[]
{
new ColumnDefinition("ContactID", typeof(int)),
new ColumnDefinition("Name", typeof(string), maxLength: 100),
new ColumnDefinition("Email", typeof(string), maxLength: 255),
new ColumnDefinition("Score", typeof(decimal)) { NumericPrecision = 5, NumericScale = 1 },
});
await writer.RenameTableAsync("Contacts", "People");
await writer.DropTableAsync("People");
DropTableAsync refuses with JetOperationException (TableInRelationship) before mutation when an enforced relationship connects the table to another table; drop that relationship first. Unenforced and self-referencing relationships are removed with the table. Targeted DAO TableDefs.Delete checks verify these cases (see Foreign-key relationships).
RenameTableAsync changes the local name of an ordinary or linked table,
including its spelling in a case-only rename. It preserves the table’s native
identity, rows, index trees, permissions, complex-item IDs and relationship
bindings. Relationship table names follow the rename. Saved queries, lookup
sources and stored expression text retain their native definitions; as with a
DAO rename, a definition can retain an old name. The library never executes
those definitions or applies Access UI Name AutoCorrect. The operation supports
statement rollback and explicit transactions.
New table, column, index, relationship and linked-table names follow the Access naming rules: 1 to 64 characters, not only white space, no leading space, and none of . ! ` [ ] or a control character (U+0000–U+001F and U+007F). Spaces inside or at the end of a name, quotes, #, = and non-ASCII letters are fine. CreateTableAsync, RenameTableAsync (the new name), AddColumnAsync, RenameColumnAsync (the new name), CreateIndexAsync, RenameIndexAsync (the new name), CreateRelationshipAsync, RenameRelationshipAsync (the new name) and the CreateLinked*TableAsync methods (the local name) throw ArgumentException for any other name before writing anything, and CreateTableAsync also rejects two columns whose names differ only by case, since Access compares names ignoring case. A linked table’s foreign name, such as dbo.Orders or orders.csv, is not checked against these rules. On a Jet3 database every new name, and a linked table’s foreign name, path and connect string, must also be in the database’s code page (see above). Names that an existing database already holds are only looked up, so a table or column written with such a name by another tool can still be read, written, altered and dropped, and a column can be renamed to a valid name.
Set ColumnDefinition.TextCollation to choose General97 for Jet3, or GeneralLegacy / General for Jet4 and ACE. Leaving it null uses the database header’s default. Text, Memo and calculated Text columns accept the option; other types and incompatible format choices refuse before mutation.
new ColumnDefinition("Name", typeof(string), maxLength: 100)
{
TextCollation = TextCollation.General,
},
The choice controls stored index keys. Expression comparisons use the database’s collation. Add, drop and rename preserve surviving columns’ stored sort orders. ColumnMetadata.TextCollation reports a supported encoder family; TextSortOrderId and TextSortOrderVersion expose the raw descriptor fields. An unknown sort order has a null family with its raw fields retained. Jet3 has no version field. These choices do not change text encoding or provide complete locale/key coverage; see the native evidence.
A decimal column is an Access Decimal with the declared NumericPrecision and NumericScale, and a value is rounded half to even to that scale when it is stored. Without them it is Access’s default Decimal(18,0), which holds whole numbers only, so 95.5 would be stored as 96. Set IsCurrency for an Access Currency column instead, on any format. Currency keeps four decimal places, rounding half to even past them, and holds -922,337,203,685,477.5808 to 922,337,203,685,477.5807; a value outside that range throws OverflowException. ColumnMetadata.IsCurrency reports Currency columns, so copying a column’s metadata into a ColumnDefinition creates the same type.
new ColumnDefinition("UnitPrice", typeof(decimal)) { IsCurrency = true },
new ColumnDefinition("Weight", typeof(decimal)) { NumericPrecision = 10, NumericScale = 3 },
Jet3 (Access 97) databases have no Decimal type, so on a Jet3 .mdb a decimal column is created as Currency. Its values then keep four decimal places whatever NumericScale says, and the precision and scale are neither stored nor applied to values. Currency’s largest value, 922,337,203,685,477.5807, leaves room for every value with up to 14 digits before the decimal point, so a declared NumericScale above 4, or a NumericPrecision more than 14 above NumericScale, throws NotSupportedException before anything is written. That includes Decimal(15,0) and the default Decimal(18,0): on Jet3, declare a precision and scale that fit, such as Decimal(18,4) or Decimal(10,2), or set IsCurrency for Currency’s full range.
ACCDB tables support Access calculated fields. Set IsCalculated and
CalculationExpression; the CLR type selects the result type. For example:
new ColumnDefinition("Quantity", typeof(int)),
new ColumnDefinition("UnitPrice", typeof(double)),
new ColumnDefinition("Total", typeof(double))
{
IsCalculated = true,
CalculationExpression = "[Quantity] * [UnitPrice]",
},
Inserts and updates recompute the cached result and its dependent calculations;
caller-supplied calculated values are ignored. Readers expose native cached
values, including CalculatedError values, without running expressions.
Column renames update references, and dropping a referenced column is refused.
Calculations use the writer’s captured regional settings for numeric and date
text, month/day names and system week starts. To select a profile explicitly,
set AccessWriterOptions.CalculatedExpressionCulture, for example to
CultureInfo.GetCultureInfo("de-DE"). The default captures CurrentCulture
when the writer opens. Each writer owns a snapshot of those settings.
The six native-qualified profiles use captured Access date/name defaults across
platforms; observable differences from runtime defaults remain caller overrides.
Invariant and other profiles retain their runtime-based behavior. See
regional settings
for the exact normalization boundary.
Access table calculations have their own expression language: they do not admit every function available in queries, defaults or validation rules. Jet3 and Jet4 MDB files cannot contain calculated fields. See the native language, storage and resource limits.
ColumnDefinition accepts four optional constraints in addition to Name/ClrType/MaxLength:
await writer.CreateTableAsync("Contacts", new[]
{
new ColumnDefinition("ContactID", typeof(int)) { IsAutoIncrement = true, IsNullable = false },
new ColumnDefinition("Name", typeof(string), maxLength: 100) { IsNullable = false },
new ColumnDefinition("Score", typeof(int))
{
DefaultValue = 0,
ValidationRule = v => v is int i and >= 0 and <= 100,
ValidationRuleExpression = ">=0 And <=100",
ValidationText = "Score must be between 0 and 100.",
Description = "Test score (0-100).",
},
});
| Constraint | Persisted in the file? | Notes |
|---|---|---|
IsNullable |
✅ MSysObjects.LvProp (Required) |
An insert whose value is still null after defaults and AutoNumber values are applied, and an update that sets the column to null, throw InvalidOperationException. An explicit null on insert is never replaced by a default, so it is rejected even when the column has one, as in Access. Restored on reopen; surfaced to readers via ColumnMetadata.IsNullable. |
AllowZeroLength |
✅ MSysObjects.LvProp |
Controls empty strings independently of Null for ordinary Text, Memo and Hyperlink fields. New declarations default to false; set true to accept "", including in a required field. An explicit stored False rejects an empty supplied/defaulted string with JetConstraintException (ZeroLengthViolation). Imported fields with no stored property impose no empty-string constraint. ColumnMetadata.AllowZeroLength returns the stored Boolean, or null when absent or inapplicable. |
IsAutoIncrement |
✅ TDEF flags: integer 0x04, GUID 0x40; GUID default in MSysObjects.LvProp |
Supported for short, int and Guid; declaring it on byte or long is refused. Integer inserts generate for null, DBNull.Value, DbDefault.Value or omission; the next value follows max(TDEF counter, existing values), and deleting a high value does not rewind it. New GUID declarations persist GenGUID() and generate only for omission or DbDefault.Value; imported GUID fields honor their actual default, including its absence; explicit Null follows the field’s nullability and primary-key constraints. A non-nullable CLR POCO property holding 0 (integer) or Guid.Empty is omitted as a mapping convention; positional/named rows can store Guid.Empty explicitly. GUID keys may be updated, subject to indexes/relationships. Integer updates can assign explicit values and raise the high-water counter, but cannot assign Null. |
IsPrimaryKey |
✅ TDEF logical-index entry with index_type = 0x01 |
Shortcut for synthesizing a PK IndexDefinition named "PrimaryKey" from one or more columns (in declaration order). Forces the PK key columns to IsNullable = false on the emitted TDEF. Mixing this with an explicit PK IndexDefinition in the same call throws ArgumentException. Single- and multi-column PKs both participate in live B-tree leaf maintenance (composite-key path). |
DefaultValue |
✅ as a literal DefaultValue expression in MSysObjects.LvProp |
CLR object stored when an insert leaves the column out (a RowValues row that does not name it, a POCO with no property for it, or DbDefault.Value); an explicit null / DBNull.Value stores null. Unless DefaultValueExpression is set, it is also persisted as a literal expression (7, "text", True, #2024-02-29 08:30:00#), for later readers; native acceptance remains type/context-specific. Text, Boolean, numeric, DateTime and Guid values persist; other types such as byte[] throw NotSupportedException at table creation. Every writer evaluates the literal and converts it to the column’s type: a DateTime default to the whole second, and a number of any CLR type as its literal reads back in the column’s type. A number the column’s type cannot hold (1e39 on a Single column, 300 on a Byte column), NaN and infinities throw ArgumentException. DBNull.Value means no default. Updates never apply defaults. Not allowed on AutoNumber, calculated, Attachment or multi-value columns (CreateTableAsync and AddColumnAsync throw ArgumentException); stored defaults on integer AutoNumber, calculated and complex columns are kept but never applied. New GUID generated columns persist the native GenGUID() default for omitted/defaulted inserts; imported GUID fields honor their actual stored default or its absence. |
ValidationRule |
⚠️ this writer only | Checked against every non-null value an insert supplies or an update assigns; a rejection throws ArgumentException. A CLR Func<> cannot be serialized into the file, so a writer that opens the database later does not enforce it. For a rule every writer and Microsoft Access enforce, set ValidationRuleExpression. |
DefaultValueExpression |
✅ MSysObjects.LvProp (DefaultValue) |
Jet expression string (e.g. "0", "\"hi\"", "=Now()", "Date()"). Every AccessWriter evaluates it when an insert leaves the column out or passes DbDefault.Value; an explicit null stores null. Blank text is treated as absent. Non-blank text wins over DefaultValue for persistence; the declaring writer still uses a CLR DefaultValue when both are set. Not allowed on AutoNumber, calculated, Attachment or multi-value columns, as for DefaultValue. Surfaced to readers via ColumnMetadata.DefaultValueExpression. |
ValidationRuleExpression |
✅ MSysObjects.LvProp (ValidationRule) |
Access rule with the column as implicit left operand (e.g. ">=0 And <=100", "Is Not Null", "Between 1 And 10", "In (1,2,3)", "Like \"A*\"", "0 Or >100"). Every AccessWriter checks it against each value an insert stores and each value an update assigns; a rejection throws ArgumentException. Like Access, a rule that does not test for Null accepts Null. Surfaced via ColumnMetadata.ValidationRuleExpression. |
ValidationText |
✅ MSysObjects.LvProp (ValidationText) |
User-facing message Access shows when ValidationRuleExpression rejects a value; the writer appends it to the ArgumentException message. Surfaced via ColumnMetadata.ValidationText. |
Description |
✅ MSysObjects.LvProp (Description) |
Free-text column description shown in Access Design View. Surfaced via ColumnMetadata.Description. Preserved across AddColumnAsync / DropColumnAsync / RenameColumnAsync. |
Stored column defaults and validation rules are enforced when their expressions belong to the supported subset. For example, older versions of Access gave every Number column a default of 0, so an insert that leaves such a column out stores 0; an explicit null still stores null, as in Access SQL. The writer interprets a supported subset of default and rule expressions with its calculated-column engine. For example, a default of =Date()+7 stores the date a week from today and a rule of >=Date()-30 rejects older dates. Native subtype, locale and context-specific function semantics remain under verification; see calculated expression notes. It also reads single-quoted text (a default of 'N/A') and VBA &H/&O literals (a rule of <=&HFF). An expression that uses unsupported syntax or functions (such as DLookUp), or cannot produce a value of the required type, refuses the write before mutation. GenGUID() generates a GUID. Declared defaults and field/table validation rules reject Access application functions such as CurrentUser, Eval and domain aggregates, as native table constraints do. Calls in unused branches are also rejected; a stored constraint containing one refuses evaluation. Table-level validation rules are enforced against complete candidate rows, including unchanged fields on updates. Null rule results are accepted; false or unevaluable rules refuse the write.
Use CreateIndexAsync to build an index over rows already in a table:
await writer.CreateIndexAsync("Contacts", new IndexDefinition("IX_Name", "Name")
{
IsUnique = true,
IgnoreNulls = true,
});
The declaration supports the same composite keys, descending columns, primary
keys and required keys as CreateTableAsync. Existing rows must satisfy the new
index; a duplicate or forbidden null refuses the complete statement. Index names
are unique within the table, ignoring case, and a table can have one primary key.
The operation currently rewrites the table using the same preservation path and
limits as column changes, so its cost scales with
the table’s rows. Existing indexes, relationships, the table identity and its
unchanged column NameMap survive.
An explicit transaction can roll the operation back.
Use RenameIndexAsync to change an ordinary or primary-key index name:
await writer.RenameIndexAsync("Contacts", "IX_Name", "IX_ContactName");
Renaming changes the logical definition and matching index property targets;
the table rows, index trees and foreign-key partner identities stay in place.
Case-only renames change the stored spelling. Names must follow Access naming
rules and be unique within the table, ignoring case. Conflicting orphan index
property targets refuse the rename before mutation. Rename relationship indexes
through RenameRelationshipAsync. Explicit rollback restores the original bytes.
Use DropIndexAsync("Contacts", "IX_ContactName") to remove an ordinary index or
an unreferenced primary key. A relationship or complex-column reference index
must remain until its owning relationship or column is removed. Dropping an
index leaves each column’s own Required property unchanged. Index deletion
uses the table rewrite path, preserving rows, other indexes and the column
NameMap; explicit rollback restores the original database.
public class Contact
{
public int ContactID { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public decimal Score { get; set; }
}
await writer.InsertRowAsync("Contacts", new Contact { ContactID = 1, Name = "Alice", Email = "alice@example.com", Score = 95.5m });
await writer.InsertRowsAsync("Contacts", new[]
{
new Contact { ContactID = 2, Name = "Bob", Email = "bob@example.com", Score = 88.0m },
new Contact { ContactID = 3, Name = "Carol", Email = "carol@example.com", Score = 92.3m },
});
await writer.InsertRowAsync("Contacts", new object[] { 4, "Dave", "dave@example.com", 77.1m });
await writer.InsertRowsAsync("Contacts", new[]
{
new object[] { 5, "Eve", "eve@example.com", 91.0m },
new object[] { 6, "Frank", "frank@example.com", 85.4m },
});
RowValues)Positional object arrays match columns by position, so a reordered array silently
corrupts data. RowValues matches by column name (case-insensitive) instead.
Omitted columns take the column’s default value, or database null when it has none
(an AutoNumber column still generates its next value), so the order you assign
columns is irrelevant:
await writer.InsertRowAsync("Contacts", new RowValues
{
["Email"] = "grace@example.com",
["Name"] = "Grace",
["ContactID"] = 7,
// Score omitted -> its default value, or null when it has none
});
// Fluent form and bulk insert
await writer.InsertRowsAsync("Contacts", new[]
{
RowValues.Create().Set("ContactID", 8).Set("Name", "Heidi"),
RowValues.Create().Set("ContactID", 9).Set("Name", "Ivan"),
});
For ordinary columns, inserts follow Access SQL: a value you supply is stored as given, so null and DBNull.Value store NULL even in a column that has a default, and a NOT NULL column rejects them. A column the insert leaves out gets its default: leave it out of a RowValues row or a POCO, or pass DbDefault.Value in any insert.
await writer.CreateTableAsync("Scores", new[]
{
new ColumnDefinition("Id", typeof(int)) { IsAutoIncrement = true },
new ColumnDefinition("Score", typeof(int)) { DefaultValue = 0 },
});
await writer.InsertRowAsync("Scores", new object?[] { null, null }); // Id 1, Score NULL
await writer.InsertRowAsync("Scores", new object?[] { null, DbDefault.Value }); // Id 2, Score 0
await writer.InsertRowAsync("Scores", new RowValues { ["Score"] = 5 }); // Id 3, Score 5
await writer.InsertRowAsync("Scores", new RowValues { ["Id"] = null }); // Id 4, Score 0
An AutoNumber column generates its next value for null, DBNull.Value and DbDefault.Value alike. Calculated columns derive their cached results from the stored source values on every insert and update; supplied calculated values are ignored. A POCO property whose value is null stores NULL; to take the default instead, leave the property off the type or mark it [NotMapped]. A non-nullable numeric property mapped to an AutoNumber column, such as int Id, counts as not supplied while it holds 0, so the column generates its next value, as Entity Framework treats a generated key; any other value is stored as given, and an int? property generates for null. To store 0 itself, insert the row as RowValues or object[]. Updates never apply defaults, and UpdateRowsAsync rejects DbDefault.Value.
Single-column convenience overloads cover the common case:
int updated = await writer.UpdateRowsAsync("Contacts", "ContactID", 1,
new Dictionary<string, object?> { ["Score"] = 99.9m });
int deleted = await writer.DeleteRowsAsync("Contacts", "ContactID", 3);
For multi-column, range, and set filters, build a RowCriteria from one or more
ColumnPredicate conditions combined with logical AND. The new values are supplied
as a RowValues:
// WHERE Region = 'West' AND Score > 80
int promoted = await writer.UpdateRowsAsync(
"Contacts",
RowCriteria.Where("Region", "West").And(ColumnPredicate.GreaterThan("Score", 80m)),
new RowValues { ["Tier"] = "Gold" });
// DELETE WHERE Score BETWEEN 50 AND 90
await writer.DeleteRowsAsync("Contacts",
RowCriteria.Where(ColumnPredicate.Between("Score", 50m, 90m)));
// DELETE WHERE ContactID IN (1, 3, 5) AND Region IS NULL
await writer.DeleteRowsAsync("Contacts", new RowCriteria
{
ColumnPredicate.In("ContactID", 1, 3, 5),
ColumnPredicate.IsNull("Region"),
});
ColumnPredicate supports EqualTo, NotEqualTo, GreaterThan,
GreaterThanOrEqual, LessThan, LessThanOrEqual, Between, In, IsNull, and
IsNotNull. Ordered comparisons coerce the operand to the column’s runtime type, so
passing an int operand against a decimal column compares correctly.
Compatible direct-row updates preserve the existing row slot and every unassigned stored byte, including retained MEMO/OLE descriptors. Equal-width field edits and their null/Boolean bits use generated bounded patch operations. Explicit assignments use the field encoder even when decoded values compare equal; only unassigned storage retains its original representation. If the row location and all encoded index keys remain unchanged, the writer leaves index pages alone, including relationship-backed indexes. Unsupported layouts, overflow rows and incompatible widths retain the replacement path; unchanged external descriptors survive relocation only when retaining all original roots is safe.
A necessary equality on a single-column ascending unique Int32 index can seek update/delete candidates, with residual criteria and bookmark checks. Every live row layout on a selected mutation page is checked before target admission; unrelated row values and LVAL payloads are not decoded. Updates that assign index keys or involve calculated columns retain complete validation scans. Deletion still performs the ownership work required to reclaim storage; an indexed target does not imply bounded total delete I/O.
Update and delete release external LVAL storage for reuse. By default, deleted payload bytes can remain in the file until overwritten; enable secure erase to clear them during deletion.
await using var writer = await AccessWriter.OpenAsync(
"database.accdb",
new AccessWriterOptions
{
SecureEraseMode = SecureEraseMode.DeletedRowsAndFreedPages,
});
await writer.DeleteRowsAsync("Contacts", "ContactID", 3);
int scrubbed = await writer.ScrubFreePagesAsync();
long truncated = await writer.ShrinkDatabaseAsync();
SecureEraseMode.DeletedRowsAndFreedPages overwrites deleted row bodies and the deleted rows’ MEMO/OLE LVAL data. Access can pack several long values onto one LVAL page, so an LVAL page returns to the Access global free list, overwritten, only when no other live value is left on it; otherwise only the deleted value’s rows on it are overwritten and marked deleted. ScrubFreePagesAsync overwrites pages already on the free list. ShrinkDatabaseAsync truncates free pages from the physical end of the file; it does not renumber live pages or perform a full Access Compact & Repair rebuild. File-backed shrinking keeps original raw tail-page images and its truncation intent in the adjacent recovery journal until its result is committed. After a process crash, preserve that journal and reopen with AccessWriter to finish recovery.
// Append a new column. Existing rows receive DBNull for the new column.
await writer.AddColumnAsync("Contacts", new ColumnDefinition("Phone", typeof(string), maxLength: 32));
// Rename an existing column. Row data is preserved.
await writer.RenameColumnAsync("Contacts", "Score", "Rating");
// Drop a column. Its data is permanently lost.
await writer.DropColumnAsync("Contacts", "Phone");
Adding a calculated column populates its cached result on every existing row. As in Access, this checks the table validation rule without reapplying field Required or validation rules to the old rows. Surviving calculated caches are retained; a new expression that depends on a stale calculated field receives the native dependency error. See the native addition controls.
These operations rewrite the whole table (copy rows to a new schema, then swap the catalog entry). Cost scales with row count. A rename may change only the letter case of a name (
ScoretoSCORE), and is then handled like any other rename, as described below. A new name that another column of the table has, in any case, throwsInvalidOperationException, and renaming a column to the name it already has, spelled as stored, changes nothing and does not rewrite the table. Native DAO3.6 controls verify case-only rename in Jet3/Jet4. Native acceptance of the unchanged-name no-op and ACE case-only rename remains separate evidence work; see the remaining schema requirements in PLAN.md. Supported rewrites keep column types, sizes, precision and scale, Unicode compression and calculated expressions, and indexes and foreign-key relationships follow the rebuilt table: its relationship index entries are re-created and the related tables are re-linked to it, and renaming a key column updatesMSysRelationships. Supported persistedMSysObjects.LvPropentries are carried forward, including unmodeled columnCaption,Format,AllowZeroLengthorAppendOnlyand table-levelDescription,ValidationRuleandSubdatasheetName. Complete unknown schema/property and application-object preservation remains open. A dropped column’s properties go with it, a renamed column’s move to its new name, an added column gets the properties itsColumnDefinitiondeclares, and the table-levelNameMapis dropped. Renaming a column also renames it in every calculated expression, validation rule and default value expression of the table that names it:[Score]or a bareScorebecomes[Rating], so they keep evaluating. A reference qualified by the table’s own name keeps the qualifier:[Contacts].[Score]becomes[Contacts].[Rating], andContacts.ScorebecomesContacts.[Rating]. Supported same-table qualifiers in calculated fields are evaluated before and after rename. Table-levelValidationRulereferences are also rewritten on rename, and dropping a field that the rule names is refused. Add, rename and drop preserve storedFilterandOrderByentries unchanged, including references to renamed or removed columns, following native DAO3.6 edits and compact in Jet3/Jet4. The API does not apply Access UI Name AutoCorrect. Fresh DAO120 controls also verify ACE display, lookup and saved-query preservation; additional native/application variants and Access UI Name AutoCorrect remain open under S9 in the project plan. Dropping a column that a relationship uses as a key column throwsInvalidOperationException; drop the relationship first. Dropping a column that another column’s calculated expression, validation rule or default value expression names throwsInvalidOperationExceptiontoo, naming that column and its expression; change or drop that column first. Dropping a column also drops the indexes it is a key column of; every other index is carried over with its settings. A table with an index the writer cannot maintain, such as one that names a column the table does not have or lies past the end of a damaged table definition, makes all three operations throwJetLimitationExceptionbefore anything is written, rather than lose that index.
Linked tables are catalog-only entries that point at data living in another source. The library can create and enumerate Access, ODBC, and text linked-table entries. Managed reads follow Access-file links and supported delimited text/CSV links through explicitly authorized source streams; ODBC links are metadata-only. Text links currently materialize delimited fields as string columns and support HDR=YES/NO, FMT=Delimited, FMT=CSVDelimited, FMT=TabDelimited, and FMT=Delimited(<char>). Ragged linked-text rows are normalized to the resolved column set: missing fields become empty strings and extra fields are ignored by row materialization. Header names are normalized; linked-text values follow DAO text-driver trimming by removing leading spaces outside quoted fields and trailing spaces after CSV unescaping. ODBC links write a parseable MSysObjects.LvProp property block; supply remote source columns when you want a generated linked-schema cache, or supply an Access/DAO-authored LvProp payload when you need byte-for-byte engine-authored metadata. Access/DAO-authored payloads are the source-of-truth fixture bytes; generated writer payloads are subjects under test, not oracles.
Table aliases and catalog identities must be unique. Access/text link IDs and
negative ODBC IDs identify catalog objects, not physical TDEF pages. Access/text
links require a source path and foreign table/file name: strict reads reject
incomplete entries, and lenient reads omit them. Native ODBC entries can expose
cached schema without connection metadata. Missing required catalog columns are
CorruptCatalog errors in every mode, and storage I/O failures propagate.
Linked execution requires AccessReaderOptions.LinkedSourceResolver. It receives detached catalog metadata, an absolute path hint and cancellation, and returns a fresh authorized readable, seekable stream. Ownership transfers to the library, which reads that exact stream and disposes it on success, failure or cancellation. Catalog paths select hints only: the library never opens them, the returned FileStream filename, a lockfile or a recovery journal. Text links request the final file once; stored Windows separators are normalized and lexical device/drive-relative paths and text-directory escapes are refused before calling the resolver.
The application must supply an isolated, committed source snapshot and authorize the actual resource, for example by selecting immutable bytes registered from a trusted source before reading the host catalog. Checking a metadata path and then opening it is insufficient because symlinks, hard links and concurrent renames can change its target. The portable library deliberately does not infer physical identity from path spelling; different aliases cannot grant access to anything beyond the returned capability. Nested Access links inherit the resolver and resource budgets. Lexical cycle checks and a maximum depth bound recursion even when aliases hide physical cycles. ODBC cached metadata remains readable, but every data-execution API refuses ODBC links, including empty queries. APIs requiring local index or complex-item storage refuse linked execution rather than reading cached host storage.
LinkedTextMaxSourceFileBytes checks the supplied stream length and limits consumption even if it grows; one excess byte may be read to detect the limit. LinkedSourcePasswordResolver is called only after an authorized Access stream is returned; host credentials are never forwarded automatically. See resolver configuration.
// Linked Access table (MSysObjects type 6) — references a table in another .mdb / .accdb file.
await writer.CreateLinkedTableAsync(
linkedTableName: "RemoteOrders",
sourceDatabasePath: @"C:\Data\Backend.accdb",
foreignTableName: "Orders");
// Linked ODBC table (MSysObjects type 4) — references a table over an ODBC connection.
// The "ODBC;" prefix is added automatically when omitted.
await writer.CreateLinkedOdbcTableAsync(
linkedTableName: "LinkedSalesOrders",
connectionString: "ODBC;DRIVER={SQL Server};SERVER=db.example.com;DATABASE=Sales;Trusted_Connection=Yes",
foreignTableName: "dbo.Orders");
// Generated ODBC schema cache: provide the source table shape so the writer can
// create table and column property targets in MSysObjects.LvProp.
await writer.CreateLinkedOdbcTableAsync(
linkedTableName: "LinkedSalesOrdersWithCache",
connectionString: "ODBC;DRIVER={SQL Server};SERVER=db.example.com;DATABASE=Sales;Trusted_Connection=Yes",
foreignTableName: "dbo.Orders",
sourceColumns:
[
new ColumnDefinition("OrderId", typeof(int)) { IsPrimaryKey = true, IsNullable = false },
new ColumnDefinition("CustomerName", typeof(string), maxLength: 100),
new ColumnDefinition("Total", typeof(decimal)) { NumericPrecision = 18, NumericScale = 2 },
]);
// Advanced ODBC path: supply an Access/DAO-authored cached-schema LvProp payload
// when you need Access/DAO-compatible catalog metadata for the linked source.
await writer.CreateLinkedOdbcTableAsync(
linkedTableName: "LinkedSalesOrdersCached",
connectionString: "ODBC;DRIVER={SQL Server};SERVER=db.example.com;DATABASE=Sales;Trusted_Connection=Yes",
foreignTableName: "dbo.Orders",
cachedSchemaLvProp: cachedSchemaLvPropBytes);
// Linked CSV table (MSysObjects type 6) — reads rows from the text file on demand.
await writer.CreateLinkedTextTableAsync(
linkedTableName: "LinkedOrdersCsv",
sourceDirectoryPath: @"C:\Data\Exports",
foreignFileName: "orders.csv",
connectString: "Text;HDR=YES;FMT=Delimited");
DataTable csvRows = await reader.ReadTableAsync("LinkedOrdersCsv", cancellationToken: cancellationToken);
ODBC links remain metadata-only. Fixed-width text links and schema.ini type inference are not part of the managed text reader. Use
ListLinkedTablesAsync()to enumerate linked entries and inspect theirKind,ConnectString,SourcePath, andSourceObjectNamemetadata.
RenameTableAsync changes a link’s local name. DropTableAsync removes its local
catalog entry, permissions and unenforced relationship references. Neither
operation opens the linked source or changes its rows. Native cached definitions
and external metadata remain allocated when complete incoming-reference evidence
is unavailable; removing a link does not promise to scrub that retained storage.
Attachments and multi-value entries expose their native flat-table Id. Use that
ID with the parent table, complex column and typed parent key to change one item:
var key = new Dictionary<string, object?> { ["Id"] = 12 };
await writer.ReplaceAttachmentAsync("Documents", "Files", key, attachment.Id,
new AttachmentInput("updated.pdf", fileBytes));
await writer.RemoveAttachmentAsync("Documents", "Files", key, attachment.Id);
await writer.ReplaceMultiValueItemAsync("Documents", "Tags", key, tag.Id, "reviewed");
await writer.RemoveMultiValueItemAsync("Documents", "Tags", key, tag.Id);
Replacement retains the item ID and parent reference. Removal does not reuse the ID. Each operation maintains child indexes and counts atomically, including inside an explicit transaction. A missing item, wrong parent or ambiguous parent key refuses the operation. Parent predicates use complete typed values and the stored text collation; numeric operands are not rounded into a match, and a fixed binary key must include its stored padding. IDs are scoped to their complex column.
On an existing native append-only Memo, inserts and explicitly assigned updates
record Access-compatible history in the parent statement. Assigning the current
value records another version; an omitted Memo that remains Null has no version.
Explicit Null and empty-string assignments record the native U+0000 history payload.
GetMultiValueItemsAsync on the history column returns the version’s Id,
MemoColumnName, Value and local-wall-clock Modified time. Several Memo fields
can share one history column. History records are read-only; update the Memo field
to record a new version. Renaming a Memo retargets its history, and dropping it
removes its history payload while preserving surviving Memo histories.
Declare a relationship between two existing tables. The library appends one row per FK column to the MSysRelationships catalog (which Microsoft Access reads to populate the Relationships designer) and emits the matching per-TDEF foreign-key logical-index entries on both sides so the relationship is visible to readers immediately. On Jet3 (Access 97) files the entries take the layout Access 97 writes.
DropRelationshipAsync and RenameRelationshipAsync rewrite MSysRelationships as live rows, update or remove the TDEF entries on every format, and maintain the matching Type=8 relationship object in MSysObjects atomically. Rename retains its identity and permissions; drop removes that object and its matching MSysACEs rows, allowing immediate same-name recreation. Ambiguous or shared catalog identities refuse before mutation.
A table in an enforced relationship with another table cannot be dropped: DropTableAsync refuses the operation with JetOperationException before mutation. Unenforced relationships and self relationships are removed atomically with the table, matching DAO. Drop enforced relationships first; AccessReader.ListRelationshipsAsync lists them:
foreach (RelationshipMetadata rel in await reader.ListRelationshipsAsync())
{
if (rel.PrimaryTable == "Orders" || rel.ForeignTable == "Orders")
{
await writer.DropRelationshipAsync(rel.Name);
}
}
await writer.DropTableAsync("Orders");
Runtime referential integrity is enforced on InsertRowAsync / UpdateRowsAsync / DeleteRowsAsync for any relationship created with EnforceReferentialIntegrity = true (the default); CascadeUpdates and CascadeDeletes honour the cascade flags. See the Limitations section for caveats.
An insert must give each non-null foreign key a value the primary table has; a null foreign key is never checked. An update is checked, as in Access, only on the rows whose foreign key it changes: assigning other columns, or assigning a key the value it already has, does not check the key, so such an update succeeds even on a row whose key has no parent. Changing a key to a value with no parent row throws InvalidOperationException before any row is written.
When other rows reference a key, deleting its row deletes them if the relationship cascades deletes, and changing the key updates them if it cascades updates; otherwise the delete or update throws InvalidOperationException. A delete finds and checks every dependent row, through every level of cascades, before it deletes any row, so a refused delete changes nothing. A key update likewise finds the dependent rows of every relationship whose key it changes, checks that every row it rewrites still fits on a data page, and checks the table’s own unique indexes, before it writes any row, so a refused key update changes nothing, even when another relationship would cascade the change; a dependent row that several relationships reach is rewritten once, with every new key. A cascaded key that would duplicate a key in a unique index of the child table, which a child row left without a parent row can cause, is still refused only after the child rows are prepared, and a delete or key update that cascades into a table whose indexes the writer cannot maintain throws JetLimitationException (see Error Handling). These failures roll back the entire call, including earlier child changes; an explicit transaction keeps its earlier successful calls.
An enforced relationship whose table or key column cannot be found, which Access never leaves behind but another tool or a damaged file can, is not skipped: every write it would have to check throws InvalidOperationException naming the relationship and what is missing, before any row is written, even when another relationship would cascade first. Writes it does not constrain still succeed: an update that leaves the key alone, a delete that matches no row, and an insert with a null foreign key, unless the missing object is the foreign-key column itself, which makes every insert into the table throw. DropRelationshipAsync removes such a relationship.
// Single-column FK
await writer.CreateRelationshipAsync(new RelationshipDefinition(
name: "FK_Orders_Customers",
primaryTable: "Customers", // PK side — szReferencedObject
primaryColumn: "CustomerID",
foreignTable: "Orders", // FK side — szObject
foreignColumn: "CustomerID")
{
EnforceReferentialIntegrity = true, // default
CascadeUpdates = false,
CascadeDeletes = false,
});
// Multi-column FK
await writer.CreateRelationshipAsync(new RelationshipDefinition(
name: "FK_OrderItems_Orders",
primaryTable: "Orders",
primaryColumns: new[] { "OrderID", "Region" },
foreignTable: "OrderItems",
foreignColumns: new[] { "OrderID", "Region" }));
Requires a database that contains the
MSysRelationshipscatalog table. ACCDB databases created byAccessWriter.CreateDatabaseAsyncinclude it.CreateRelationshipAsyncreportsSystemTableMissingwhen the table is absent. Access-authored and DAO-authored databases provide the interoperability evidence; library round trips validate internal consistency.