Select Page

Admin Database (Chemistry-Cathy Comber)

History.
In the beginning PC’s used a disk operating system that used a file system called FAT.
This was a simple file system that allowed file access to all the files on the system, there was no way of stopping any one from snooping around on the system and opening files. You could hide files but any user could un-hide them and look at them.
Windows 3.1, 95, 98 and Millenium (these were 16 bit systems), used FAT file systems which were intrinsically insecure, so when Microsoft Access Database was invented it had to incorporate its own security control system. It did this by encrypting a password and access list that was in a file with an .mdw extension(usually security.mdw). All Access databases in prior to and including Access 2003 put their data into files which had the extension .mdb (standing for Microsoft Data-Base, or perhaps mysterious database?). mdb files always check to see if security has been turned on and if it has it looks for security.mdb in the place that is has been told to look. If the security.mdb is not the one it is expecting, or if it can’t find it, it gives the message that you do not have the necessary permissions.
Microsoft call this type of security User-Level Security.

Another problem to bear in mind: for some reason once the database has succeeded in authenticating against a security.mdw it copies it to the local user’s profile and uses this copy
for future transactions, so if you change the network setup you will probably get the error message again and will have to re-do the process outlined below.

In later 32bit operating systems that are based on Windows NT (eg Windows 2000 and later) you have the option to use a better File system called NT File System (NTFS). This file system controls access through lists (called Access Control Lists ACLs) that are integral within the file system. This means that the Operating System administrator can control who sees what file or directory. This is also a feature of other networked operating systems like Unix and Netware.
This makes the local database access control system redundant. You can just set up the database without any security control and rely on the operating system make sure your data is safe from prying eyes. This is much easier to manage because you do not have to continually link a database to its security.mdw file every time you make a change.

Unfortunately once you have selected to muse the MS Access MDB security it can be hard to get rid of. This is the case of the Chemistry Admin Database, somebody thought it might be a good idea to use the User-Level Security even thought it was protected by operating system Control Lists.

Solving the permissions problem with an .mdb file in MS Access:

In a secure access database that uses User-Level Security (mdb) , you will get the following message if the database cannot find the right security.mdw file:

To solve this you have to point the mdb file to the right security file. This file will have been created when the security was first turned on by the administrator of the database. The administrator will have created a list of people and their rights that are saved with in the security.mdw file.
To point to the appropriate security.mdw file, you click OK on the above message box and type CTRL-G.

This brings up the VB editor and in the Immediate window (bottom panel) you type

DoCmd.RunCommand acCmdWorkgroupAdministrator

This brings up the Workgroup Administartor
Image:Chem-Db2.png
Select to join a workgroup and browse to the relevant security.mdw file when the ‘workgroup information file’ window pops up.

Image:Chem-Db3.png

Browse to the place on the network that the security.mdw is (at present  this is “S:chemistrygroupsadminAdmin Databasenetwork”)
Then click OK and you will get the following Message if it works.

Image:Chem-Db4.png
Click OK and restart acccess and re-load the database.

NOTE : If you still get the permissions error message you might have to delete any Security.mdw files (or security1.mdw, securiyy2.mdw etc)
that are found in the local profile. In Windows 7 and Vista this is in :

C:Users<UserName>AppDataRoamingMicrosoftAccess

Getting Rid of the mdb security on a Database.

1. Start Microsoft Access.

2. Open the database that employs user-level security.

3. Log on as a workgroup administrator (a member of the Admins group).

4. Give the Users group full permissions on all tables, queries, forms, reports, and macros in the database.

5. Exit and restart Microsoft Access and then log on as Admin.

6. Create a new blank database, and leave it open.

7. Import all the objects from the original database into the new database.

8. If users will be using the current workgroup information file when they open the database, clear the password for Admin to turn off the Logon dialog box for the current workgroup. This is not necessary if users will be using the default workgroup information file created when they install Microsoft Access.
The new database is now completely unsecured. The workgroup information file that was current when the new database was created in step 6 defines the Admins group for the new database.

9. If users will be using the current workgroup information file when they open the database, clear the password for Admin to turn off the Logon dialog box for the current workgroup. This is not necessary if users will be using the default workgroup information file created when they install Microsoft Access.
The new database is now completely unsecured. The workgroup information file that was current when the new database was created in step 6 defines the Admins group for the new database.

Solving the problem of Multiple user Access: Splitting the Database: Distributing the Frontend:

Editing The Database

Future:
Access 2007 and 2010 can access and use .mdb files and manipulate them in the Access 2003 fashion. However they introduce a new file format (.accdb and .accde files) and files in these new formats no longer support User-Level Security. For Chemistry it will be a good idea to convert the
current database to the .accdb format.
There are notes on Access 2010 Security here:
http://office.microsoft.com/en-us/access-help/introduction-to-access-2010-security-HA010341741.aspx?CTT=5&origin=HP005188240#_Toc265079111

Skip to toolbar