468,167 Members | 1,856 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,167 developers. It's quick & easy.

BUG: deep hiding tables removes data from complex datatype fields

isladogs
232 Expert 128KB
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
Mar 10 '21 #1
2 1310
NeoPa
32,042 Expert Mod 16PB
You do like to get into the nitty-gritty of things.

Thanks for posting. Very interesting.
Mar 10 '21 #2
isladogs
232 Expert 128KB
I do like to understand all the hidden depths, nooks & crannies of Access!
Mar 10 '21 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by luvdairish | last post: by
5 posts views Thread by Patrick.O.Ige | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
reply views Thread by gcreed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.