Code to find hidden tables?

[Mark_Thornton]

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]

Untested:

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

 

[gemma-the-husky]

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]

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

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]

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]

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]

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]

Application.GetHiddenAttribute(acTable,"YOURTable")

 

[isladogs]

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]

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]

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

To set 'standard' hide with VBA, use:

 Application.SetHiddenAttribute acTable, "TableName", True

or to set back to visible, use

 Application.SetHiddenAttribute acTable, "TableName", False

 

[theDBguy]

Hi. Thanks for the clarification!