.accdb specific features support

[himselfv]

Original report by himselfv (Bitbucket: 557058:736ea475-fec1-499e-93e9-568104650027, GitHub: himselfv).

---

jet-tool 0.6a can open and create .accdb files but it does not support some new features. For instance:

• multi-value fields
• field version history
• attachments

Thus dumping + recreating the database that uses such features will fail.

Multi-value fields

Access 2007 introduced multi-value fields. These allow storing several identically typed values in a single field.

To make a field MVF: "Design mode" -> select a field -> "Field Properties" -> "Lookup" -> set "Display control: Combo box" and then "Allow mutiple values" to true.

Internally Access puts into MVF a key, and creates a secret table which associates this key with all values.

MVF machinery is hidden from ADOX/DAO, but confusingly shows in weird indexes and keys which can't be recreated when dumped.

Attachments

Attachments are internally simply multi-value fields where the field type is BLOB.

Multi-value fields are defined in MSysComplexColumns (details below), and one of their properties is "ComplexTypeID". This references MSysObjects table, where there are several predefined ComplexTypes:

MSysComplexType_UnsignedByte
MSysComplexType_Short
MSysComplexType_Long
MSysComplexType_IEEESingle
MSysComplexType_IEEEDouble
MSysComplexType_GUID
MSysComplexType_Decimal
MSysComplexType_Text
MSysComplexType_Attachment

Attachments are simply MVFs of type MSysComplexType_Attachment.

Field version history:

Access 2007 (or 2010) introduced append-only fields / field version history. You can still edit and delete data in such fields, but a history of all revisions is kept.

FVH can only be enabled for Memo (Long Text) fields.

To enable: "Design mode" -> select a field -> "Field Properties" -> set "Append Only" to true.

This is implemented in the same way, internally Access creates a secret table which stores all revisions for the key stored in FVH field.

Produces a strange index with a name like "VersionHistory_[some numbers]_[GUID]" which references sometimes an existing column and sometimes again something else with a GUID.

New_xxx indexes

Some indexes in Northwind.accdb are doubled as weird hidden indexes with a New_ prefix. E.g.

CustomerOverOrders - a normal visible index
New_CustomerOverOrders - a hidden weird index with unclear purpose.

MVF and FVH implementation details

MVFs and FVHs are declared in a system table MSysComplexColumns which contains references to MSysObject entries.

MSysObject lists all objects, but some are "super-hidden", invisible even to ADOX/DAO (instead of being visible with "Hidden" flags). I have not yet found a way to access these "super-hidden" secret tables (to dump their values for instance).

Perphaps to dump values we can use the recommended ways of accessing MVFs (via .values property).

It's so far unclear how to see that a field is MVF, and it's true contents (the key). ADO reports these fields by their abstracted value type (e.g. Number), not the underlying type. Since there's a way to access MVF as multiple values, there should be a way to figure their type after all.

It's also unclear how to restore MVF values via SQL. We can assign them via .values, but we want to generate SQL code...

If we had a way to access hidden tables we could just ignore the abstraction layer and dump those tables as tables, and then dump key values in MVFs themselves.