First Published 7 Oct 2022


This article was written in response to an email from another Access developer who asked:
Is it possible to disable or hide design view in ACCDB files before distribution?

When you distribute Access applications to end users, you will usually want to prevent changes to the design of database objects

The standard method of doing this is to compile the database as an ACCDE file. The Design view context menu item will then be disabled.

Design View Enabled (ACCDB)

Design View Disabled (ACCDE)

As the above screenshots show, various other context menu items are also disabled for ACCDE files.

Creating an ACCDE file also has the significant advantage that your VBA code will no longer be accessible to end users.

However, there are times when distributing an ACCDB file may be more convenient.
For example, ACCDB files can be designed to run in both 32-bit and 64-bit Access whereas ACCDE files will only run in the same bitness they were created in.

Design view cannot be disabled / hidden from Access options or the property sheet for individual database objects.
However, it is still possible to disable / hide design view in ACCDB files using very simple code which modifies the right click context menus (command bars).

Design View Disabled (ACCDB)

Design View Hidden (ACCDB)

However the developer will also need to be able to reverse that code in order to re-enable design view

Copy the code below to a standard module:

CODE:

Option Compare Database
Option Explicit

Public Function DisableDesignView()

On Error Resume Next

      Dim cb As CommandBar
      Dim cbCtl As CommandBarControl

      For Each cb In CommandBars

            If cb.type = msoBarTypePopup Then
                  For Each cbCtl In cb.Controls
                        If cbCtl.Caption = "&Design View" Then
                              'disable Design View menu item
                             cbCtl.enabled = False
                        End If
                  Next
            End If
      Next

      Set cb = Nothing: Set cbCtl = Nothing

End Function

'----------------------------------------

Public Function HideDesignView()

On Error Resume Next

      Dim cb As CommandBar
      Dim cbCtl As CommandBarControl

      For Each cb In CommandBars

            If cb.type = msoBarTypePopup Then
                  For Each cbCtl In cb.Controls
                        If cbCtl.Caption = "&Design View" Then
                              'hide Design View menu item
                             cbCtl.visible = False
                        End If
                  Next
            End If
      Next

      Set cb = Nothing: Set cbCtl = Nothing

End Function

'----------------------------------------

Public Function RestoreDesignView()

On Error Resume Next

      Dim cb As CommandBar
      Dim cbCtl As CommandBarControl

      For Each cb In CommandBars
            If cb.type = msoBarTypePopup Then
                  For Each cbCtl In cb.Controls
                        If cbCtl.Caption = "&Design View" Then
                              'enable and show Design View menu item
                              cbCtl.enabled = True
                              cbCtl.visible = True
                        End If
                  Next
            End If
      Next

      Set cb = Nothing: Set cbCtl = Nothing

End Function

NOTE:
1.   Disabling/hiding the design view will 'stick' as an application setting so the above code module can be deleted before distribution

2.   As the screenshots above show, other context menu items are not disabled for ACCDB files. Similar code can be used to disable/hide other items

3.   All module code will still be accessible. To prevent this, you should lock down the database using other security measures
      See my 3-part article Improve Security in Access Databases



Download

The module code is available in the attached (zipped) file.
After unzipping, you can then import the modChangeRes.bas file directly into the Visual Basic Editor

Click to download:   modDesignView.zip



Please use the contact form to let me know whether you found this code useful or if you have any questions.



Colin Riddington           Mendip Data Systems                 Last Updated 7 Oct 2022

Return to Code Samples Page

Return to Top