protect records in tables in MS Access

[mulamootil]

Hi - I have created multiple tables and forms for a database. We are entering data into the tables using forms which in turn are linked to items on a switchboard. I would like multiple users in different departments to have access and enter data using the forms. The items on the switchboards are created for different departments and I would like to password protect each of these items so that it is accessible by respective depts. I am not sure if it is poosible to password protect each item on a switchboard. Could someone please help. I am new to MS Access 2007 or any database management programs.

Also, how can we protect a record in the table from getting deleted by somebody.

Thanks in advance.

[MMcCarthy]

I am not aware of any way of doing this with the built in switchboard but then I never use it as I build all my own menus which allows me to set any extras into the code behind the buttons as I build.

[Stewart Ross]

Hi, and Welcome to Bytes!

On your concerns about protecting individual records from deletion, Access is not designed to provide record-level access rights. With SQL Server and other client-server back-end databases there are many choices for user access rights, but not with Access I'm sorry to say. You would have to implement user-level security measures yourself - there is no automatic way to do so, and in Access 2007 the limited security features that had been available in earlier versions of Access were discontinued.

Similarly, to use bespoke switchboards you will need to have custom user security available, programmed to offer different users different views of the forms etc.

One approach is to identify individual users by using custom functions to retrieve their network log-in IDs or user names, and on opening the database use the network ID of the current user to determine permissions and other settings from data you store in a custom user table which you will need to maintain and administer.

There is no automated way I know of to do all this, as Access simply does not have built-in features for handling such bespoke security.

-Stewart

[ADezii]

You can 'sort of' Password Protect every Switchboard Option. Simply have the Switchboard Items call Public Functions within which Password functionality can be incorporated. If the User passes the test, the code will fall through, if not you can simply Exit the Function.

[mulamootil]

I am not aware of any way of doing this with the built in switchboard but then I never use it as I build all my own menus which allows me to set any extras into the code behind the buttons as I build.

Okay thank you. Let me try that. Do you have to write a code once you have created your own switchboard.

[nico5038]

I use my own menu's too and have a login table with username and the authorization level. This level is the trigger to show the main menu for that type of user.

Creating your own menu is really easy and gives full flexibility.

An alternative might be to create multiple front ends from one "main" front-end database that hold all the functions for that specific department. That way they never can access "wrong" forms as they aren't there...

Nic;o)

[MMcCarthy]

Or the other way I like to do it is set user level access and enable/disable menu buttons based on the users access level. Then although the user can see all options on the menu they can ony select those that are enabled for them.

[TheSmileyCoder]

I dont think you can password protect your forms without using some bit of Visual Basic for Applications (VBA) coding.

To prevent users deleting posts the forms (atleast it did in 2003) have an option "Allow deletions" which can be yes or no. This would of course apply to all users of the form. Again, its possible to provide password/user specific access to such a delete function but it requires VBA coding. Not terribly complicated coding, but coding nonetheless.

[mulamootil]

Hi, and Welcome to Bytes!

On your concerns about protecting individual records from deletion, Access is not designed to provide record-level access rights. With SQL Server and other client-server back-end databases there are many choices for user access rights, but not with Access I'm sorry to say. You would have to implement user-level security measures yourself - there is no automatic way to do so, and in Access 2007 the limited security features that had been available in earlier versions of Access were discontinued.

Similarly, to use bespoke switchboards you will need to have custom user security available, programmed to offer different users different views of the forms etc.

One approach is to identify individual users by using custom functions to retrieve their network log-in IDs or user names, and on opening the database use the network ID of the current user to determine permissions and other settings from data you store in a custom user table which you will need to maintain and administer.

There is no automated way I know of to do all this, as Access simply does not have built-in features for handling such bespoke security.

-Stewart

Thanks Stewart. I created my own menu and got a VBA code from the net to password protect each button and it worked. Thanks for your help.

Stan

[mulamootil]

I dont think you can password protect your forms without using some bit of Visual Basic for Applications (VBA) coding.

To prevent users deleting posts the forms (atleast it did in 2003) have an option "Allow deletions" which can be yes or no. This would of course apply to all users of the form. Again, its possible to provide password/user specific access to such a delete function but it requires VBA coding. Not terribly complicated coding, but coding nonetheless.

Thank you so much . I used VBA code and it worked.

Stan :)

[mulamootil]

You can 'sort of' Password Protect every Switchboard Option. Simply have the Switchboard Items call Public Functions within which Password functionality can be incorporated. If the User passes the test, the code will fall through, if not you can simply Exit the Function.

Thank you very much. Yes I used a code by creating my own menu instead of switchboard and it worked.


Stan