User permissions in Microsoft Access 2010

Question

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?

Solution 1

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:

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

Solution 2

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"

Comments

[enderland]

It would be wonderful if this explained how to actually compile Access into an executable.