Allowing everyone to have full access to a database you created can bring on many issues. In most cases, too many queries and reports get created, which makes it hard at some point for users to find what they are looking for, which then leads to more queries and reports being created. Unfortunately, without the use of VBA, it is difficult to fully lock down an Access database, but there are some settings that can cut down on what users are allowed to see and use.

One way is to allow only formatting ribbon tabs, i.e., the Home and File tabs, to be used by everyone. To do this, follow these steps:

1. With the database open, click the File tab, then click Options at the bottom of the left-hand menu
2. Once the dialog box appears, click Current Database
3. Scroll to the Ribbon and Toolbar Options and uncheck Allow Full Menus and Allow Default Shortcut Menus
4. Click OK to save the changes – The changes will not take effect until you close and reopen the database
5. Once the database is reopened, the only ribbon tabs shown are File and Home
6. To undo this, click the File tab and select Privacy Options – this will open the Options dialog box.
7. Follow steps two and three, recheck the boxes for the two settings, then click OK to save
8. Close and reopen your database, and the ribbon will be back to normal

Another way to restrict users is to change what is available in the Navigation Pane, which looks like this:

Note that objects, such as Tables, Queries, etc., are available for users to use in the Navigation Pane by default. We can remove the Navigation Pane by following these steps:

1. With the database open, click the File tab, then click Options at the bottom of the left-hand menu
2. Once the dialog box appears, click Current Database
3. Scroll to the Navigation section and uncheck the selection for Display Navigation Pane 
4. There will be no more Navigation Pane, which means that users will be forced to use the buttons/macros you created for anything they want to do in the database

If you are going to build a database and restrict users from going haywire, you need to make sure you bring potential users in on the planning stage, as this will be vital for a user-friendly database that has the functionality required to get the job done. Restriction is a good thing, when it comes to databases, as good functionality will depend on it, but it is also important to remember that databases are built for everyone’s use. So it needs to be built for everyone else and not just you.

Strengthen your database skills with an Access class from AdvantEdge Training & Consulting in Denver or Phoenix.