455
This isn't so much a question but information about an obscure bug in Access ...and a workround
Last year, a fellow Access developer reported this to me as I work extensively with what I call 'deep hidden' tables using property dbHiddenObject (MSysObjects Flags=1).
If a table containing data in attachment or multi value fields is 'deep hidden', the data APPEARS to be deleted from those fields. The data is NOT restored if the tables are returned to normal.
I hadn't experienced this issue as I never use MVF or attachment fields (nor would I ever recommend anyone else does so).
The developer concerned was panicking as a huge amount of complex data seemed to have been lost for ever
I tested this issue and confirmed it to be true.
However, I also checked the contents of the related deep hidden system tables (each beginning with f_...) and the actual complex data is still present. However the user tables seem to have become detached from these.
NOTE: By design, deep hidden system tables starting with f_ cannot normally be viewed either as a table or by using a query but there is a way of doing so which I used here as a check
Anyway, I found a workround to this bug by pure chance and the developer was able to retrieve the 'lost' data.
I decided to test the third complex datatype - column history in long text/memo fields - by adding an extra field to the same table. To my surprise, the attachment / MVF field data was immediately restored.
The column history field doesn't need to be populated - it just needs to exist.
Once that extra complex datatype field is added, deep hiding the table does NOT cause further 'loss of data' from the MVF/attachment fields.
I have absolutely no idea why it works however! Each complex datatype has its own distinct deep hidden system table (all with names starting f_) but these are not connected to each other in any way
Whilst it is unusual, to say the least, to use complex datatypes with deep hidden tables, it is a bug that will hopefully be easy for MS to fix.
I reported the bug to Microsoft about 6 months ago via Access User Voice and at another forum
MS have confirmed they can replicate both the bug and workround. It has been added to their jobs list but, as it isn't in any sense high priority, the fix will certainly not be implemented in the near future
In the meantime, if anyone is interested enough to look into this, what I'd really like to know is why the workround I discovered actually works.
For more info on the various types of system table, see my extended article: Purpose of system tables
