Code to find hidden tables? (1 Viewer)

Mark_Thornton · Oct 31, 2011

Hello everybody! My name is Mark, I'm new here, and I am hoping that someone can help me with this VBA problem...

I am working in Access 2003 and need some VBA code to identify tables that are both hidden and linked, in another Access 2003 database. My code loops through all the tables and tests each one for the attributes of being linked and hidden, using the following 2 functions:

Function IsTableHidden(objTable As TableDef) As Boolean
IsTableHidden = (objTable.Attributes And dbHiddenObject)
End Function

Function IsLinkedTable(objTable As TableDef) As Boolean
IsLinkedTable = (objTable.Attributes And dbAttachedTable)
End Function

The function IsLinkedTable worked fine, but IsHiddenTable did not. Further investigation revealed that, for a table that is both linked and hidden, objTable.Attributes.Value = dbAttachedTable.

I was expecting that objTable.Attributes.Value = dbAttachedTable + dbHiddenObject, but this seems to be untrue. Is my expectation wrong, or have I discovered a bug in Access 2003?

As dbHiddenObject does not seem to be recorded in the Attributes, I feel thoroughly stuck: how else can I discover if a given table is hidden, or not?

All help and advice will be extremely welcome. Thanks in advance to anyone who contributes to this thread.

Best wishes
Mark

PeterF · Oct 31, 2011

Untested:

Code:

Function IsTableHidden(objTable As TableDef) As Boolean
   IsTableHidden = GetHiddenAttribute(acTable, objTable.Name)
End Function

gemma-the-husky · Oct 31, 2011

not checked, but maybe "hidden" is a separate boolean flag, and not an attribute in the way that "attached" is an attribute.

[no - it's not that .. just checked]

so, I am not looking into this, but I expect the hidden attribute is a "metatag" of some sort in the application, rather than an attribute of the tabledef object.

I decided to look a bit further, and i think you should be correct - so I am playing around with it now myself.

gemma-the-husky · Oct 31, 2011

Ok - this table gives the values of the constants

http://msdn.microsoft.com/en-us/library/bb225819(v=office.12).aspx

I just set a table as hidden, but the attributes value is still zero, not 1.

I therefore think that maybe dbhiddenobject is probably not referring to the same property "invoked" when you check "hidden" against the table definition.

Out of interest, I just tried ennumerating the properties of the table object in the "tables" container. I got a different set of properties, including date created/ date modified etc - but I couldn't see the hidden object indicator, or anything that might have included the hidden object.

This code

Code:

Sub showit()
Dim prp As Property
Dim db As Database
Dim doc As Document
Set db = CurrentDb
Set doc = db.Containers("tables").Documents("yourtable")
For Each prp In doc.Properties
    MsgBox (prp.Name & " " & prp.Value)
Next
End Sub

gemma-the-husky · Oct 31, 2011

finally found it in this thread.

http://www.pcreview.co.uk/forums/table-attribute-hidden-true-t2672869.html

Application.SetHiddenAttribute acTable, "sometable", true

note that it doesn;t tell you how to read the value - just how to set it. also discusses a bug with using the tabledef attribute

Mark_Thornton · Oct 31, 2011

Dear Dave <gemma-the-husky>,

Many thanks for your contributions - your investigations match my findings, i.e. the "Hidden" property can be set programatically, but it is a struggle to read that property.....

...the search for a solution still goes on!

Best wishes

Mark Thornton

djnickcarter · Mar 14, 2013

Try:

Dim TD As TableDef

For Each TD In CurrentDb.TableDefs

If TD.Properties(dbHiddenObject) = True Then
MsgBox TD.Name & " is hidden."
End If

Next TD

GohDiamond · Dec 18, 2019

Application.GetHiddenAttribute(acTable,"YOURTable")

isladogs · Dec 18, 2019

Just to hopefully clarify some of the earlier answers on this ancient thread which was just resurrected by GohDiamond.

There are two very different ways of hiding tables
a) hiding in the navigation pane - Hidden Object = True.
For info, this adds 8 to the MSysObjects Flags value for the table.
Such objects can be made visible again by ticking Show Hidden Objects
b) 'deep hiding' the table using the dbHiddenObject attribute. This sets the Flags value =1 and the tables cannot be made visible in the navigation pane.

NOTE: It is possible to combine attributes for table defs

I'm deliberately omitting some details. Hope that hasn't confused anyone.

theDBguy · Dec 18, 2019

isladogs said:
Just to hopefully clarify some of the earlier answers on this ancient thread which was just resurrected by GohDiamond.

There are two very different ways of hiding tables
a) hiding in the navigation pane - Hidden Object = True.
For info, this adds 8 to the MSysObjects Flags value for the table.
Such objects can be made visible again by ticking Show Hidden Objects
b) 'deep hiding' the table using the dbHiddenObject attribute. This sets the Flags value =1 and the tables cannot be made visible in the navigation pane.

NOTE: It is possible to combine attributes for table defs

I'm deliberately omitting some details. Hope that hasn't confused anyone.

Hmm, so which one was GohDiamond referring to with GetHiddenAttribute, a or b? Probably a, right?

isladogs · Dec 18, 2019

Hi DBG
I think you know the answer to that. It is indeed a)

To set 'standard' hide with VBA, use:

Code:

 Application.SetHiddenAttribute acTable, "TableName", True

or to set back to visible, use

Code:

 Application.SetHiddenAttribute acTable, "TableName", False

theDBguy · Dec 18, 2019

Hi. Thanks for the clarification!

Code to find hidden tables? (1 Viewer)

Mark_Thornton

New member

PeterF

Registered User.

gemma-the-husky

Super Moderator

gemma-the-husky

Super Moderator

gemma-the-husky

Super Moderator

Mark_Thornton

New member

djnickcarter

New member

GohDiamond

"Access- Imagineer that!"

isladogs

MVP / VIP

theDBguy

I’m here to help

isladogs

MVP / VIP

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread