Have you ever wanted to hide a table, query or other database object from prying eyes?

Today, I thought we explore a few of the available options to do so. I’d like to explore:

• The Navigation Pane
• USys Naming Prefixes
• The Hidden Object Attribute
• Setting the TableDefs Attributes With dbHiddenObject

The Navigation Pane

First and foremost, our first line of defense when trying to hide elements of our database is to hide the navigation pane. Normally, in a well-developed application, users do not have direct access to the navigation pane, but rather we create a custom ribbon or menu system in which we control what they can see and access.

If they can’t see the Objects, they can’t mess around with them in any way. Well, not the typical users at least.

USys Prefixes

If you prefix any database object name with the term ‘USys’, so the name of the objects starts with ‘USys’, then it does not natively appear in the navigation pane unless the user specifically selects the option to Show System Objects.

So by simply renaming a table from ‘Contacts’ to ‘USysContacts’, the table no longer is listed in the Navigation Pane unless the user has specifically set the option ‘Show System Objects’
 

The Hidden Object Attribute

Another approach would be to set the object’s Hidden attribute. By setting this property the object will not natively appear in the navigation pane unless the user specifically selects the option to Show Hidden Objects.

This can be set manually or via VBA code.

Manually

Hiding An Object

1. Right-Click on the database object
2. Select …Properties (Table Properties, Query Properties, Object Properties, View Properties, …)
3. Check the Attributes: ☐ Hidden checkbox
4. Click on OK

Unhiding/Showing an Object

1. Right-Click on the database object
2. Select …Properties (Table Properties, Query Properties, Object Properties, View Properties, …)
3. Uncheck the Attributes: ☐ Hidden checkbox
4. Click on OK

VBA Automation

To automate the process, we can simply use the SetHiddenAttribute method.

Application.SetHiddenAttribute method (Access)

The SetHiddenAttribute method sets the hidden attribute of an Access object.

Hiding An Object

Application.SetHiddenAttribute acTable, "TableName", True		'A Table
Application.SetHiddenAttribute acQuery, "QueryName", True		'A Query
Application.SetHiddenAttribute acForm, "FormName", True		'A Form
Application.SetHiddenAttribute acReport, "ReportName", True		'A Report
Application.SetHiddenAttribute acMacro, "MacroName", True		'A Macro
Application.SetHiddenAttribute acModule, "ModuleName", True	'A Module

Unhiding/Showing an Object

Application.SetHiddenAttribute acTable, " TableName ", False		'A Table
Application.SetHiddenAttribute acQuery, " QueryName ", False		'A Query
Application.SetHiddenAttribute acForm, "FormName", False		'A Form
Application.SetHiddenAttribute acReport, "ReportName", False		'A Report
Application.SetHiddenAttribute acMacro, "MacroName", False		'A Macro
Application.SetHiddenAttribute acModule, "ModuleName", False	'A Module

Checking An Object’s Current Hidden Attribute Status

Furthermore, we can determine the current state of any object by using the GetHiddenAttribute method, for instance:

? Application.GetHiddenAttribute(acTable, "TableName")

Application.GetHiddenAttribute method (Access)

The GetHiddenAttribute method returns the value of a hidden attribute of a Microsoft Access object in the object's Properties dialog box ...

Setting the TableDefs Attributes With dbHiddenObject

Another approach would be to change the TableDefs Attributes to Include dbHiddenObject.  This is not the same attribute as what we set in the previous section.  They are 2 distinct things event though they both go by the name Attribute!

By doing so, the table will no longer be displayed in the navigation pane at all even if the Show System Objects and/or Show Hidden Objects are enabled. It does however remain accessible through the TableDefs collection and the MSysObjects table. Even with the dbHiddenObject attribute set, the table continues to operate just fine in queries, forms, … It is just no longer visible in the Navigation Pane in any shape or form.

Another advantage of this technique is that once a table is hidden in this manner, if you try to link to it via another database using External Data… the table doesn’t even appear.

Hiding A Table

Set db = CurrentDb
With db.TableDefs("TableName")
    .Attributes = .Attributes Or dbHiddenObject
End With
Set db = Nothing
Application.RefreshDatabaseWindow

In my testing, this worked and other times failed on the same PC with the same db?! So you can always try a simplified version which isn’t ideal, but always worked when I used it:

CurrentDb.TableDefs("TableName").Attributes = dbHiddenObject
Application.RefreshDatabaseWindow

Note that anytime you refresh linked tables, you must reapply the hiding process.

Unhiding/Showing a Table

The following should work, but doesn’t!

Dim db As DAO.Database
Set db = CurrentDb
With db.TableDefs("TableName")
    If .Attributes And dbHiddenObject Then
        .Attributes = .Attributes Xor dbHiddenObject
    End If
End With
Set db = Nothing
Application.RefreshDatabaseWindow

instead, simply do:

CurrentDb.TableDefs("TableName").Attributes = 0
CurrentDb.TableDefs("TableName").RefreshLink
Application.RefreshDatabaseWindow

Checking The Current State Of A Table

You can check the current status of a table by doing something like:

? (CurrentDb.TableDefs(TableName).Attributes And dbHiddenObject)

where it returns True if the table has the dbHiddenObject attribute, this is hidden, and False when it does not and is visible.

In the past, there were issues that if a table was set with dbHiddenObject and you compacted the database, it would be deleted/removed from the database. That said, to date, I never experienced this issue in my usage.

All of this to say that TableDefs().Attributes is very iffy. So use at your own risk and backup, backup, backup! As always, don’t mess around with system tables!

Use the Attributes property for TableDef objects - Office

Describes the usage and code example of Attributes property for TableDef objects in Access.