I do a fair bit of programming in Access 2007, after having started in Access 97 and moved on to 2000. The 2007 versions of Office programs had many significant changes, including the Ribbon, but one change that slipped under most people’s radar was the removal of user-level security. Access 2007 files no longer used the “users-and-groups-in-a-workgroup-MDW-file” method of providing security; in fact, it pretty much did away with security on a per-object basis altogether.
Reaction to this was mixed. Sure, user-level security could be confusing and a hassle to implement well, necessitating a lot of administration to make sure that every table, query, form, and report had the right access levels for each user or (hopefully) group you had. But, as cumbersome as this system was, many people thought that it was at least something, and eliminating it without providing an alternative was a mistake. However, Microsoft did not care to provide such an alternative.
This left a bit of a problem for people who create applications in Access: sometimes it is necessary to prevent people from accessing data or forms based on who they are, and with the new version of Access without user-level security, a curious user could open up any form you’ve got by going to the Navigation Pane and double-clicking it. Sure, you can hide that pane (and it’s a good idea to do so), but security through obfuscation is not good security.
Fortunately, as far as forms go, there is a workaround. You can programs forms to only open if they are opened in the way that YOU want them to be opened, in the correct order, from other forms. The key is the openargs argument for the OpenForm command. This passes any arbitrary value to the form when opened. Here, I’m passing the hex value 0xDEADBEEF when opening MyForm:
DoCmd.OpenForm "MyForm", acNormal, , , , , &HDEADBEEF
The corresponding bit of code to catch this value is in the Form_Open event of the opened form:
If IsNull(Me.OpenArgs) Then'Do something hereCancel=TrueEnd If
Setting Cancel equal to True will cancel the opening of the form. The key trick here is that any form you open by just double-clicking on in the Navigation Pane will not have a null openargs value, and thus will be cancelled. Only if you open the form programmatically will it open.
It’s up to you how to decide who gets what: I have a custom login box that I use to get a person’s name, and then enable/disable buttons on my main switchboard to only grant them access to those forms they are entitled to. Even if they found the other forms in the Navigation Pane they couldn’t open them without going through my switchboard.
It is by no means a perfect solution, as there are still a number of ways to get around it. But it is more secure than the defaut.