First Published 15 Jan 2025

This is the fourth in a series of articles about the complex datatypes that were first introduced in Access 2007.
This article discusses the deep hidden attached tables that are required to make these work correctly.
It also covers an issue when deleting tables with column history memo fields as one of the system tables is not removed by the deletion process.

The three previous articles were:
•   multivalued fields (MVFs)         - see Multivalued Fields . . . and why you shouldn't use them
•   column history memo fields     - see Use Column History in Memo fields
•   Attachment Fields                     - see Attachment Field Issues

The next article in this series will look in more detail at the 9 complex datatypes available and how these are handled by Access.



Complex fields and Attached Data

Complex datatypes (attachment fields/ multivalued fields / column history in memo fields) allow you to store multiple items of data in a single field.
This appears to violate the principles of database normalization, can lead to data redundancy and make your database harder to maintain.

To make this work, Access creates additional deep hidden attached tables for each complex data field. These are used to store the complex field information as normalized data.
With one exception, the attached tables have names starting 'f_' followed by a long string then another underscore and the complex field name e.g. f_D4788154E1BE40D191EA5D3A0529C43E_Attach

A list of these tables can be seen by querying the read only MSysObjects system table:

SELECT Name, Type, Flags
FROM MSysObjects
WHERE ([Type]=1) AND ([Flags] In (262144,-2146828288)) OR ([Name] Like 'MSysComplexTypeVH*');

In this example, a total of 16 attached tables are needed to support 4 tables with complex fields.


Another query gives more details about the tables, fields and properties involved. This will be explained in the next article in this series


The deep hidden attached tables are automatically created by Access whenever a new complex field is added to a table.
However, the tables cannot be directly viewed from the navigation pane, cannot be accessed in a query or edited by any standard method.
This obscures effective data management, making it more challenging to manage the normalized data.

For example, the screenshot below shows a table (tblAttach) with an attachment field (Attach), together with the deep hidden attached table (f_. . . ._Attach) required to make this work.


NOTE: In order to display the attached table contents above, I have made use of an obscure approach which is not widely known about. I do not intend to explain it here.

The next screenshot below shows a table (tblMVF) with two multivalued fields (MVF_Color and MVF_Lookup) together with the two deep hidden attached tables (f_. . . _MVF_Color and f_. . . MVF_Lookup) required to make these work.


In each case, you can see that the attached tables contain one record for each of the complex field data items.

If a table with attachment fields or multivalued fields is deleted, the attached tables are also automatically deleted at the same time.

However, the third complex datatype,column history, works in a slightly different way. It requires two deep hidden tables in order to function correctly:
a)   An attached normalized data table with a name starting f_ . . . _. This has one record for each column history item showing the history over a period of time.
b)   An associated system table with a name starting MSysComplexTypeVH_. In all my tests, this table has ALWAYS been empty! I have no idea what purpose it has.


In this case, deleting the column history table, tblColHist, results in a different outcome.

a)   The normalized data table with a name starting f_ . . . ._ is also deleted.
b)   The empty table with a name starting MSysComplexTypeVH_ is NOT deleted and remains as an orphan

The MSysComplexTypeVH_. . . table behaves more as though it is 'associated' rather than properly attached.
Deleting the main table causes it to become detached and left behind.

I have tried various methods of removing this table including:
a)   Using VBA code such as DoCmd.DeleteObject acTable, "MSysComplexType_VH . . ."
b)   Using VBA code to change this to a standard table before deleting it
c)   overwriting the table with another identically named standard table then deleting it
d)   using DDL to create an identically named standard table and deleting that

In each case, the process failed.
This was EITHER because Access refused to recognize the table so it could be deleted OR because the table already existed so it couldn't be overwritten or re-created! A classic Catch-22!

As far as I am aware, the ONLY workaround to remove these tables is to import all objects ito a new empty database. The orphaned table(s) are NOT imported.

I recently contacted the Access team about this issue, They acknowledged that leaving orphaned MSysComplexHistoryVH_ tables behind is a long-standing bug.
At some point it may get fixed, but as there is a workaround, I think this is unlikely to be a priority.

To replicate this issue:
1.   Create / import or link a column history table, Check in MSysObjects and you will see the three tables as described above
2.   Now delete the column history table. Check again in MSysObjects. The MSysComplexTypeVH_. . . table is NOT deleted

Although the MSysComplexTypeVH_. . . table is both hidden and empty, I don't like the idea of leftover objects that I cannot remove.
If anyone DOES know a way of removing this table, please let me know!



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 15 Jan 2025

Return to Access Blog Page

Return to Top