User permissions in Microsoft Access 2010

3
votes

I have a database in Access 2010 where I've created a navigation form, and I want a regular user opening the database to enter a password and only have access to that form, and not to the actual tables or to the navigation pane. They should only be able to view, add, edit, and delete data through the forms I've prepared, and I don't want them to be able to get to the tables themselves unless they have an admin password.
Is there any way to create user permissions or not?

3
ms-access
Not with the *.accdb file format. You can do quite a bit to keep the incurious user from interfering by disabling menus, database passwords and compiled (.accde) front-ends, but it will not keep the determined tamperer out. - Fionnuala
You may also find this question informative. - Gord Thompson

3 Answers

1
votes

You can certainly consider using what in Access is called ULS (User Level Security). This feature has been depreciated for accDB format files, but you are free to use ULS with mdb format files and Access 2010.

Read this document, and then read it again. Microsoft Access Security FAQ.

I should also point out that the "act" of preventing the user from seeing tables, or designing forms is RARE a use of the security model. The security system is to define who can open what forms, or who can open what tables, or who can open what reports. I suppose some overlap in the goal of preventing users from seeing code or modifying forms is possible with ULS, but it is NOT NORMALLY the use of such a security model.

To prevent modifying forms, code, reports etc., then the usual approach is to compile the Access application into what is called a Access executable (accDE for 2007 and beyond, and prior to a mde).

This compile step prevents users from modifying common objects like forms, reports and VBA code. And with a few lines of code you can hide all of the Access UI including the ribbon (or perhaps even build a custom ribbon). So with correct startup settings, and this line of code:

You hide the ribbon with this ONE LINE of code:

DoCmd.ShowToolbar "Ribbon", acToolbarNo

The resulting Application looks like this:

enter image description here

The above was done with one line of code – the rest is just simple startup settings. Note how you see the desktop behind the above screen shot, but none of the Access UI.

You can also disable the shift key by-pass on startup. This means when the application is launched, your forms and custom UI appears. The user will NOT be able to get "behind the" scenes, nor switch into design mode.

Best regards

1
votes

It would be wonderful if this explained how to actually compile Access into an executable. – enderland Sep 30 '13 at 13:38

File > Save & Publish > Save Database As: "Make ACCDE"

0
votes

With Microsoft access, you cannot fully restrict a user for viewing tables. You can still backdoor hiding forms or overriding permissions by using shortcut menus on the top of the application. Access has some serious limitations because the whole file itself must have modify permissions at the folder level in order to open. You can compile a file to block access to the VBA code and form design only.