First Published 8 Apr 2022                 Last Updated 27 Oct 2022

Section Links:
       The Challenge
       The Problem
       Background Info
       Flags and Attached Tables
       My Tests
       My Solution
       Downloads
       Summary
       Recommendations
       Video
       Further Reading



The Challenge:                                                                                                                     Return To Top

This is the fifth in a series of security challenge articles discussing issues related to ways of helping secure Access apps.

In previous security challenges, I have provided example databases and challenged readers to try and break the security.
The level of difficulty varied from simple to highly complex.

However, this security challenge is different.
It is based on a thread posted at Access World Forums by abdullahkhan in May 2020: Hiding Tables Delete Attachment

In this example, I will describe the problem caused by the forum user adding security to certain database tables, together with the solution.
The challenge this time is to explain why the solution works.



The Problem:                                                                                                                       Return To Top

•   Data in attachment fields is ‘lost’ when the table is deep hidden
•   Making tables visible again does not restore the data

Before I provide my solution (as in post #4 of the above thread), some additional explanation would probably be useful



Background Info:                                                                                                                 Return To Top

In Access 2007, three new complex datatypes were added:

1.   Multivalued fields (MVFs)


2.   Attachment fields (a special type of MVF)


3.   Column History Memo fields



Each of these allowed users to attach multiple items to the same record (the opposite of standard database practice)

The datatypes are complex because the information is actually stored in deep hidden attached tables as normalised data.
There is one attached table (starting f_..) for each complex field and an additional system table (starting MSysComplexTypeVH..) for each column history field. These are highlighted in the screenshot below.

NOTE:
I am NOT recommending that any developer should use these complex datatypes. These have many issues (see the section below).



Flags And Attached Tables                                                                                                 Return To Top

Table info is stored in the Flags field of the READ ONLY system table MSysObjects.
For example:
      •   standard local table: Flags = 0
      •   hidden table: Flags = 8
      •   deep hidden table: Flags = 1
      •   complex table: Flags = 262144
      •   deep hidden attached table: Flags = -2146828288

These values CANNOT be edited directly as the table is READ ONLY. Any changes are done using code.
However, I do NOT intend to explain the code to deep hide tables here as this table property is often used for security


By default, very few of these tables will be visible in the navigation pane.
However additonal tables wil be shown if Show Hidden Tables and / or Show System Tables are ticked in Navigation Options

Default View

Show Hidden Objects

Show System Objects

Show Hidden & System Objects

The deep hidden tables are NEVER displayed in the navigation pane.
Users have no direct access to the deep hidden attached tables.

However, it is possible to understand how this works using a query on the complex tables. For example, querying the attachment field data:


The query results are identical to the table (shown above)


However if the '+' sign is clicked to expand the query design, 3 fields from the attached table are shown. These can be added to the query:


The query results now show additional records with one attached file per record:



The same process can be done with multivalued field data:


The query results are identical to the MVF table (shown above)


However if the '+' sign is clicked to expand the query design, the MVF field from the attached table is shown and can be added to the query:


The query results now show additional records with one MVF item per record:



For column history memo field data, the query doesn't expand to show the attached table fields:


The query results are identical to the column history table (shown above).
However right clicking on a column history memo field record gives a Show Column History context menu item.
Click to show a read only popup message listing the column history.



It is often easier to view the historical data using a form



It is certainly true that both attachment and multivalued fields have proved popular with many new Access users.
This is partly because their use is heavily promoted in the various Access template databases provided by Microsoft.
However, most experienced developers STRONGLY recommend avoiding using each of these.

The column history memo field feature is far less widely used, though it causes far fewer issues

Amongst the main reasons against the use of complex fields are that:
•   It is difficult to manage the data which is held in inaccessible tables
•   The data structure is disguised and running action queries on them is difficult or in some cases not possible
•   Attachment fields significantly bloated the database size and affected performance
•   Tables containing these complex datatypes cannot be upsized to other databases such as SQL Server

The thread at Access World Forums exposed a different issue.

Some developers set tables containing sensitive data to be deep hidden.
As stated above, deep hidden tables cannot be viewed in the navigation pane, even when hidden & system tables are made visible.

Doing this can make such tables more secure against users tampering with their databases for whatever reason.
Deep hiding tables should NOT affect the data stored in them.

However, as the Access World Forums thread described, when tables containing attachment fields were deep hidden, the attachment field data is ‘lost’.
Understandably, the OP was very concerned about their lost data.



My Tests:                                                                                                                             Return To Top

I ran tests on each of the three complex datatypes listed above as well as a standard local table.
The screenshots below show each of these before being deep hidden:


Firstly, I set the table with an attachment field to be deep hidden and was able to replicate the issue described in the forum thread.
Next I tested a multivalued field and found it was affected in exactly the same way.
Making the tables visible again did NOT restore the data.

However, the column history memo field data was NOT ‘lost’ when its table was deep hidden.



My initial response was that this issue provided yet another good reason for avoiding both attachment & multivalue fields.

The tables were now standard tables and no longer listed as complex.


However, I was able to inspect the deep hidden tables and found the attachment & MVF data still existed.
It appeared that the data had become detached from the deep hidden 'attached tables'.

This was confirmed when I again tried to run the queries including fields from the now 'detached' tables.
These queries now produced a parameter prompt for each of those fields indicating the tables could no longer be found





This is clearly a bug and will have existed ever since complex datatypes were introduced with Access 2007.
To be fair to Microsoft, it is unlikely that anyone expected developers to deep hide tables containing complex data.



My Solution:                                                                                                                         Return To Top

I then decided to test by adding a column history memo (CHM) field to a table with an attachment field that had previously been deep hidden.

A column history memo field requires the datatype to be Long Text (memo) and the Append Only property set to Yes.



When I did so, the ‘lost data’ immediately reappeared! It wasn’t even necessary to populate the CHM field.


I re-tested by adding a column history memo (CHM) field to a table with ‘lost’ MVF data.



Again the lost data was recovered.



Adding the column history field had effectively re-attached the complex fields to the source data in the attached tables.
However the tables were still listed as standard tables. Something clearly didn’t make sense.

So why does the unexpected fix work? That is my challenge to anyone reading this thread



Downloads:                                                                                                                         Return To Top

An example database has been provided so you can test the above series of events

Both 32-bit & 64-bit versions have been supplied as ACCDE files

Click to download the correct version for your computer:
     Complex Deep Hide Text (32-bit ACCDE)      (zipped)
     Complex Deep Hide Text (64-bit ACCDE)      (zipped)



SUMMARY:                                                                                                                           Return To Top

•   The problem – tables containing attachment or MVF data lose that data if the tables are deep hidden
•   The unexpected fix – add a column history memo field – the data is recovered / retained
•   The challenge (mystery) – explain why the unexpected fix works



RECOMMENDATIONS (in order of preference!):                                                             Return To Top

1.   Don’t use attachment or multivalued fields. Other better methods exist for handling such data
2.   If you really MUST use either of these complex datatypes, do NOT deep hide the tables
3.   If you really, really MUST deep hide those complex tables, make sure you include a column history memo field . . . even if you don’t actually need it!



VIDEO                                                                                                                                   Return To Top

To see this in action, please watch the accompanying video on my YouTube channel: A Deep Hidden And Complex Access Attachment Mystery or you can click below:

       



If you subscribe to my Isladogs on Access channel on YouTube, you will be notified whenever new videos are released.



Further Reading:                                                                                                                 Return To Top

The following articles give additional information relevant to this topic;

Multivalued Fields . . . and why you really shouldn't use them!
Issues With Lookup Fields in Tables
Use Column History to store historical data in long text (memo) fields
Purpose Of System Tables



I hope you will be able to explain this mystery. How does the column history field restore lost data in another field? Or is it just a glitch?

Please provide feedback on this article by sending an email using the feedback form provided below.



Colin Riddington                       Mendip Data Systems                       Last Updated 27 Oct 2022