How to Password Protect an Access Table

All I wanted to do was set up a password that would restrict access to specified tables in a .mdb or .accdb file. I hoped it would just be a case of right-clicking the tables and putting some basic encryption on them (like in Excel perhaps). I found some VBA online that apparently does this, but it is too messy looking.

So I just had to try and do it through Access' "Users and Permissions" functionality. It is pretty complicated for what I wanted-which is just a safety measure against different users entering their data in the wrong table really. But after much thinking, reading Access" help file, and tinkering around I managed to do what I wanted. If you can follow these Wizard instructions, you should be able to set up the passwords quite quickly.

Password Protect an Access Object

What the wizard does is set up a workgroup.

  1. Open the file you want to secure
  2. Go to "Database Tools", "Users and Permissions", "User-level Security Wizard"
  3. Select "Create a New Workgroup Information File" then click "Next"
  4. Check the location/name, enter an ID, select "I want to create a shortcut to open my security-enhanced database", click "Next"
  5. Just leave all the objects checked and click "Next" again
  6. No need to add any new groups here-just click "Next"
  7. Don"t let the Users group have any permissions-so just click "Next"
  8. In this tab, select your name in the left pane and add a password and PID; create however many other user accounts you need, including a password and PID for each one (in this case abh, hch, and wh = three additional user accounts); click "Next"
  9. Just leave yourself, the default user (e.g. "cgoldie") in the "Admins" group-don"t add anyone else to any groups right now. Just leave them as basic users. Click "Next"
  10. Check the destination folder for the workgroup information file you just created. Hit "finish" and be sure to somehow print or save the "One Step Security Wizard" report that Access generates.
  11. Close the database and re-access it by clicking on the shortcut that was just created (on your desktop?) You will now have to enter a password you just created to get into the .mdb file. Enter as the default user (i.e. the one who is a member of the "Admins" group).
  12. Go to "Database Tools" then "Users and Permissions" and select "User and Group Permissions
  13. On the "Permissions" tab, with the "Users" radio button selected, select the first user in the left pane who you would like to grant some access permissions to
  14. With that user still highlighted, select, in the right pane, the object you want to grant permissions to the user for. With the relevant object highlighted, check the level of access you want that user to have (e.g. amend records, delete records, etc.)

    15. permissions dialogue box
    Permissions Dialogue Box
  15. Next, select "Database" from the drop down list and check the "Open/Run" permission box for this user
  16. Repeat this step for each user, granting them the desired level of permissions to the desired object(s), and to open the database

The downside of this solution is that you have to keep three files (the .mdb file, the workgroup information file, and the shortcut file) instead of just the .mdb database itself.

For more on learning Access SQL see my article on learning sql .