LEARN MS-ACCESS TIPS AND TRICKS: Security

Running Workgroup Admin in Access2007.

If you are serious about implementing User-Level Security in Microsoft Access 2003 (mdb) databases and want to continue using them in Access 2007 security settings intact, avoid converting them to the Access 2007 format. Once upgraded, the security model changes completely, and User-Level Security is no longer supported in the accdb databases.

If you have already converted an MDB database into ACCDB format and lost all user-level security settings, the situation is not entirely hopeless. If you have a backup of the original .mdb file, you are in luck—simply restore the backup and continue using it in Access 2007, retaining your User-Level Security.

If no backup exists, you can still downgrade the database. Use the Save As… option from the Office Menu (top-left corner), select an earlier format such as Access 2002–2003 or Access 2000, and save the active database as a new .mdb copy. However, this will not automatically restore the lost security settings. You must reapply them manually, assigning permissions to each database object either at the group level or for individual users.

Running the Workgroup Administrator.

In either case, you must join the Workgroup Information File (a database with the file extension .mdw) from Access 2007 to attach your database Users/Groups and continue using them with your .mdb databases, or to implement new security profiles.

When you open an .mdb database in Access 2007, the Users and Permissions menu will appear under Database Tools. However, your Database Users and Groups will not be visible under this option unless you first join the Workgroup Information File you were using in an earlier version of Access.

In Access 2003, you can do this from Tools → Security, which provides an option to run the Workgroup Administrator program to create or join a Workgroup Information File. In Microsoft Access 2000, this program is located in the Language folder (…\1033 for U.S. English) of MS Office:

C:\Program Files\Microsoft Office\Office\1033\WrkgAdm.exe

Running this utility allows you to join the appropriate Workgroup Information File and manage security accordingly.

However, in Microsoft Access 2007, none of these menu options are available. The only way to access the Workgroup Administrator is by running the following command, either from VBA or directly in the Immediate Window:

DoCmd.RunCommand acCmdWorkgroupAdministrator

You can execute this command manually from the Immediate Window (open the VBA editor with Alt+F11, then press Ctrl+G to display the Immediate Window), or programmatically from a Command Button Click event on a form. This will allow you to create a new Workgroup Information File or join an existing one on the server.

Once you have joined the Workgroup Information File, you can continue using .mdb files with their existing User-level security in Microsoft Access 2007, or manually restore any lost security settings.

Technorati Tags: Microsoft Security

Earlier Post Link References:

User-level Access Security and Access2007.

In versions of Microsoft Access before Access 2007, User-level and Object-level Security were reliable methods for protecting applications. I secured all my Access applications with User- and Group-level security, which proved highly effective when the applications were shared over a network. Because of this, I never needed to lock VBA modules with a password or convert the applications into a compiled form (MDE format) to prevent tampering. User-level security provided sufficient control to assign users the access level within the application.

If you wish to continue using this feature in Access 2007, you must avoid converting earlier databases (with the .mdb extension) to the newer .accdb format. Once converted, all User-level security settings are removed and cannot be reinstated. However, you can still maintain and use User-level security in Access 2007 as long as your databases remain in Access 2003 or earlier formats (with the .mdb extension).

Database Objects and Permissions.

With User-level Security, you can control what Users can do and cannot do. Check the following table to get some idea as to how to set permissions on each object type and what they do:

Permission	Applies to these objects	Result
Open/Run	Entire database, forms, reports, macros	Users can open or run the object, including procedures in code modules.
Open Exclusive	Entire database	Users can open a Database and lock out other users.
Read Design	Tables, queries, forms, macros, code modules	Users can open the listed objects in the Design view. Note: Whenever you grant access to the data in a table or query by assigning another permission, such as Read Data or Update Data, you also grant Read Design permissions because the design must be visible to correctly present and view the data.
Modify Design	Tables, queries, forms, macros, code modules	Users can change the design of the listed objects.
Administer	The entire database, tables, queries, forms, macros, and code modules	Users can assign permissions to the listed objects, even if the user or group does not own the object.
Read Data	Tables, queries	Users can read the data in a table or query. To grant User permissions to read queries, you must also give those User permissions to read the parent tables or queries. This setting implies Read Design permission, which means that Users can read your table or query design in addition to the data.
Update Data	Tables, queries	Users can update data in a table or query. Users must have permission to update the parent table or queries. This setting implies both Read Design and Read Data permissions.
Insert Data	Tables, queries	Users can insert data into a table or query. For queries, users must have permission to insert data into the parent Tables or Queries. This setting implies both Read Data and Read Design permissions.
Delete Data	Tables, queries	Users can delete data from a table or query. For queries, users must have permission to delete data from the parent Tables or Queries. This setting implies both Read Data and Read Design permissions.