MS Access – VBA – Hide the Navigation Pane

The Basics

Here is a simple bit of code that permits you to hide the MS Access’ main object browser/Navigation Pane, to stop nosy users from accessing tables, queries, etc…

'DoCmd.SelectObject acTable, , True
DoCmd.SelectObject acModule, , True
DoCmd.RunCommand acCmdWindowHide

In conjunction with the code to determine whether the user is running the runtime or full version of Access (see MS Access – Determine if Runtime or Full Version
) you could insert a section of code such as:

If SysCmd(acSysCmdRuntime) = False Then
    'DoCmd.SelectObject acTable, , True
    DoCmd.SelectObject acModule, , True
    DoCmd.RunCommand acCmdWindowHide
End If

A Function You Say! (Update 2016-09-20)

Now transforming the above into a reusable function that we can call as we see fit, you get something like:

'---------------------------------------------------------------------------------------
' Procedure : HideNavPane
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Control the visibility of the Access Navigation Pane
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bVisible  : True/False - whether the Nav Pane should be visible or not.
'               True = Display the Nav Pane
'               False = Hide the Nav Pane
'
' Usage:
' ~~~~~~
' Call HideNavPane(True)  'Display the Nav Pane
' Call HideNavPane(False) 'Hide the Nav Pane
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-02-13              Initial Release
' 2         2012-09-25              Added runtime check -> SysCmd(acSysCmdRuntime)
' 3         2015-11-07              Switched acTable to acModule
'---------------------------------------------------------------------------------------
Public Sub HideNavPane(bVisible As Boolean)
    On Error GoTo Error_Handler

    If SysCmd(acSysCmdRuntime) = False Then
        If bVisible = True Then
            '            DoCmd.SelectObject acTable, , True
            DoCmd.SelectObject acModule, , True
        Else
            '            DoCmd.SelectObject acTable, , True
            DoCmd.SelectObject acModule, , True
            DoCmd.RunCommand acCmdWindowHide
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: HideNavPane" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

So how can this be used exactly?

Well, I make a call to a similar function as part of my AutoExec macro which is used to initialize my database so the navigation pane is hidden as soon as the database is opened.

Note

Some of you will notice that I switched from using 

DoCmd.SelectObject acTable, , True

to

DoCmd.SelectObject acModule, , True

and the reason is quite simple, I recently encountered an error trying to run the original code using acTable in a database and couldn’t figure out why. The error was in question was

Run-time error ‘2544’:
Microsoft Access cannot find the ” you referenced in the Object Name argument.

Eventually, after a cup of coffee it hit me, the db in question had no tables (it is used to process external file, but doesn’t store anything in tables)! Now I know this is a rarity, but to make the code more robust, switching it to use acModule makes much more sense, since by having this very function in the db, I can guarantee the presence of at least one Module! So there you have it. That said, for 99% of cases, the original acTable would work just fine.

10 responses on “MS Access – VBA – Hide the Navigation Pane”

  2. Kevin Hazlett

    Not using acTable, and instead using acModule, also takes care of the situation where you’re running in a split database configuration. The code doesn’t seem to like the acTable value when being run from the front-end. There is a lot of people asking why their getting the Output To command error when attempting to print. Your little trick fixes my issue every time in both runtime and development time.

    1. Daniel Pineault Post author

      Not sure what going on for you. I just tested and even when I call it from within a form, it works fine. That said, I never run it from a form either, I always run it as part of my startup code, the first thing that runs before any forms are launched. My script normally checks my linked table paths, creates persistent connections, applies security, sets database properties, … and hides the navigation pane (actually that is done early on in the sequence).

    1. Daniel Pineault Post author

      This control whether or not the Navigation Pane is visible to the user. It does not do anything to the Ribbon. For that you need to use custom ribbon xml and/or database property settings.

  6. Josh

    The true/false usage seems backwards…

    “`vba
    HideNavPain(True)
    “`
    should hide the Nav Pain not unhide the Nav Pain.

    If the function was named `UnhideNavPain` then the input `True` should hid the Nav Pain.

    1. Daniel Pineault Post author

      Yes and no. I do see your point and thought about that myself, but the input argument is named bVisible, so you set the bVisible = True when you want it visible and set bVisible = False when you don’t want it visible.