First Published 18 Jan 2025                                                Difficulty level :   Moderate


Section Links:
          Introduction
          Complex Data Fields
          Matching Complex Fields to the Attached Tables
          MSysComplexType Tables
          Feedback



Introduction                                                                                                                   Return To Top

This is the fifth in a series of articles about the complex datatypes that were first introduced in Access 2007.

This article discusses the 9 complex datatypes available in more detail and explains how these are managed by Access.

The four 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
•   deep hidden attached tables           - see Attached, associated, detached . . .



Complex Data Fields                                                                                                       Return To Top

Access allows a wide variety of datatypes to be used in tables. All those shown in RED are complex datatypes which allow multiple values to be stored in the same field



NOTE: Datatype information taken from the Microsoft article: DataTypeEnum enumeration (DAO)

The table used below includes all 9 complex datatypes:


Seven of these are number datatypes - Byte/Integer/Long Integer/Single/Double/Decimal/GUID (Replication ID). The other two are short text and attachment

To create a number or short text complex field in table design, click the Lookup tab, select combo box or list box as display control then set allow multiple values = Yes.
Next set the field row source based on a value list or a table/query:




In the table or a query, the complex field(s) will be shown as comma separated lists:


In a form, the specified display control will be used. In this example, all the controls are set as listboxes:


The query design has been expanded to show the multi-valued fields from the various attached tables used to store the multi-valued data


Perhaps surprisingly, it is possible to create an aggregate (totals) query on the multi-valued field data


However, action queries on complex data fields can be complicated to do



Matching Complex Fields to the Attached Tables                                                       Return To Top

If you have many complex field types, it can be difficult to keep track of which hidden attached tables are matched to each field

In this example, 16 attached tables are needed for the 4 tables with complex fields:


Aceess uses a system table, MSysComplexColumns to keep a track of each complex table/field and attached table.


The ColumnName references the complex field names in the various tables.
The number values in the ComplexTypeObjectID field specify the complex datatype used.
All values are between 21 and 39 with the exception of the ColumnHistory data which works in a different way:


Unlike the other complex datatypes, the ID value for ColumnHistory varies for each table/field and is negative for linked tables.

The ConceptualTableID and FlatTableID fields are used to link to the MSysObjects system table.

Using a query joining the MSysComplexColumns and MSysObjects system tables gives the attached table name for each complex datatype.


The query design is:


The query SQL used is:

SELECT DISTINCT MSysObjects.Name AS ComplexTable, MSysComplexColumns.ColumnName AS ComplexField, qryComplexDatatypesLookup.ComplexDatatype,
      MSysComplexColumns.ComplexTypeObjectID, MSysObjects_1.Name AS AttachedTableName, MSysObjects_1.Flags
FROM ((MSysObjects INNER JOIN MSysComplexColumns ON MSysObjects.Id = MSysComplexColumns.ConceptualTableID)
      INNER JOIN MSysObjects AS MSysObjects_1 ON MSysComplexColumns.FlatTableID = MSysObjects_1.Id)
      INNER JOIN qryComplexDatatypesLookup ON MSysComplexColumns.ComplexTypeObjectID = qryComplexDatatypesLookup.ComplexTypeObjectID
ORDER BY MSysObjects.Name;

The above query fails to match the additional column history system table(s) MSysComplexTypeVH_*

To do so, we need another query linking the FlatTableID field in MSysComplexColumns to the Id field in MSysObjects:


The query design is:


The query SQL is:

SELECT DISTINCT MSysObjects.Name AS ComplexTable, MSysComplexColumns.ColumnName AS ComplexField, qryComplexDatatypesLookup.ComplexDatatype,
      MSysComplexColumns.ComplexTypeObjectID, MSysObjects_1.Name AS AttachedTableName, MSysObjects_1.Flags
FROM (MSysObjects INNER JOIN (MSysObjects AS MSysObjects_1 INNER JOIN
      MSysComplexColumns ON MSysObjects_1.Id = MSysComplexColumns.ComplexTypeObjectID) ON MSysObjects.Id = MSysComplexColumns.ConceptualTableID)
      INNER JOIN qryComplexDatatypesLookup ON MSysComplexColumns.ComplexTypeObjectID = qryComplexDatatypesLookup.ComplexTypeObjectID
WHERE MSysComplexColumns.ComplexTypeObjectID < 0 Or MSysComplexColumns.ComplexTypeObjectID > 39;

Combining the two queries as a UNION query gives a list of ALL attached tables:


The query SQL is:

SELECT MSysObjects.Name AS ComplexTable, MSysComplexColumns.ColumnName AS ComplexField, qryComplexDatatypesLookup.ComplexDatatype,
      MSysComplexColumns.ComplexTypeObjectID, MSysObjects_1.Name AS AttachedTableName, MSysObjects_1.Flags
FROM ((MSysObjects INNER JOIN MSysComplexColumns ON MSysObjects.Id = MSysComplexColumns.ConceptualTableID)
      INNER JOIN MSysObjects AS MSysObjects_1 ON MSysComplexColumns.FlatTableID = MSysObjects_1.Id)
      INNER JOIN qryComplexDatatypesLookup ON MSysComplexColumns.ComplexTypeObjectID = qryComplexDatatypesLookup.ComplexTypeObjectID
UNION SELECT MSysObjects.Name AS ComplexTable, MSysComplexColumns.ColumnName AS ComplexField, qryComplexDatatypesLookup.ComplexDatatype,
      MSysComplexColumns.ComplexTypeObjectID, MSysObjects_1.Name AS AttachedTableName, MSysObjects_1.Flags
FROM (MSysObjects INNER JOIN (MSysObjects AS MSysObjects_1
      INNER JOIN MSysComplexColumns ON MSysObjects_1.Id = MSysComplexColumns.ComplexTypeObjectID)
      ON MSysObjects.Id = MSysComplexColumns.ConceptualTableID)
      INNER JOIN qryComplexDatatypesLookup ON MSysComplexColumns.ComplexTypeObjectID = qryComplexDatatypesLookup.ComplexTypeObjectID
WHERE MSysComplexColumns.ComplexTypeObjectID <0 Or MSysComplexColumns.ComplexTypeObjectID > 39;

As explained in the earlier articles, the attached tables are all deep hidden and their contents cannot be viewed by standard methods.

In order to do so, I use an obscure method which is not widely known about and which I do not intend to explain here:

These tables contain the normalized data for each complex field. The screenshot below shows the data for 5 of the tables attached to complex fields in the tblTestComplexDatatypes table


All these attached tables work in the same way.



MSysComplexType Tables                                                                                               Return To Top

To add a further level of complexity, Access has another deep hidden system table for each of the 9 complex datatypes.
Column History fields also create their own MSysComplexTypeVH tables - one for each column history field


I have viewed the contents of each of these tables for local and linked complex data together with linked complex data in SharePoint lists.

In my tests, the peculiar thing about each of these tables is that they ALWAYS empty:


Clearly the tables are required in some way, but I have absolutely no idea what their purpose is!



Feedback                                                                                                                         Return To Top

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 18 Jan 2025

Return to Access Articles Page

Return to Top