|
Multiuser Databases
By The Gunny
ALL database users must have "Full Control" permissions on the directory of a multiuser database
(Thousand Oaks, CA -- Oct. 15, 2004) -- In order for multiple users to open a database at the same time, all of these users must have "Full Control" permissions on the directory where the file is located. If any one of the users doesn't have "Full Control" permissions on the directory, which includes read, write (create), modify, and delete permissions, then the locking database file (*.LDB), or lack thereof, will interfere with the users trying to open and use the database file, because one or more of these users cannot lock records in the database.
Microsoft Access is a file-based database, not a client/server database, so no records are physically locked inside the database file. (In case you're wondering why, placing locks inside a data file will prevent all other users from reading that locked data, which puts a severe damper on the meaning of "multiuser database.") Instead, the database relies on the creation, modification, and deletion of a record locking file (*.LDB) while the database is open in "shared mode" for multiple users. This *.LDB file is placed in the same directory that contains the database file. There is no way around this architecture if you are using the Jet engine.
You may have users that are required to use the database in a "read-only" manner and are not allowed to alter the data. If this is the case, consider the six choices available when dealing with an Access database in a multiuser environment:
- Don't restrict any of the users -- Since all database users must have "Full Control" permissions on the directory which contains the database, all database users will be able to add, modify and delete records. As a precaution against accidental deletions, hide the tables and other database objects from the users, replace the default menu and toolbars with custom menus and toolbars, and ensure that the users only see the forms and reports. Customize data entry forms by locking all fields unless the user specifically selects the "Edit" or "Add" buttons, which you have provided to programmatically unlock the current record for editing or adding a new record. This will (usually) prevent accidental changes to the records. Make sure that consistent backups are made, just in case you need to replace certain records or even the entire database file when something goes wrong. This method allows multiple users to open the database at the same time in "shared-mode."
- Apply user-level security -- Implement Access user-level security to enforce the restrictions on the "read-only" users. Again, all database users must have "Full Control" permissions on the directory which contains the database. This allows multiple users to open the database at the same time in "shared-mode."
- Prevent the restricted users from opening the database -- Divide your users into "restricted" and "unrestricted" users. Since the restricted users won't be opening your database ever again, have your Windows System Administrator remove all Windows security permissions for the Windows directory on these restricted users' Windows user accounts (this means no permissions whatsoever). All of the other users are unrestricted users and are considered to be database users. Remember, all database users must have "Full Control" permissions on the directory which contains the database. Then, export the data in the database to spreadsheet files in another directory so that the restricted users can read the data elsewhere, but not make any changes to data in your database. (Don't link to the data in the database from "read-only" or "locked" spreadsheets located in another directory, because the restricted users would need "read" permissions on the database file, which your Windows System Administrator has taken away to prevent the restricted users from opening the database.) This allows the multiple unrestricted users to open the database at the same time in "shared-mode." The restricted users are carefully herded away from your database to where they can do no harm.
When the restricted users complain that they are inconvenienced by having to use the spreadsheet or can't do their jobs as well without the Access database application, explain to them that you have these priorities:
#1: To protect the data; and
#2: To ensure that the database is always used in "shared mode" when it's needed as a multiuser database.
The restricted users' convenience does not take precedence over either of these two priorities. Be firm.
- Hide the database from the restricted users -- All database users must have "Full Control" permissions on the directory which contains the database, but you can also hide the database from the restricted users and only allow them to use a special shortcut that uses the /ro (read-only) command-line switch to open the database. Ensure that you are using the full path, not just the file name, of the Microsoft executable in the shortcut, or else all of the command-line switches will be ignored when the database is opened. The syntax for this shortcut is (watch out for the word wrap):
"<FullPathToMSOffice>\MSAccess.EXE" "<FullPathToDB>\MyDB.MDB" /ro
Place the special shortcut on each restricted user's desktop (or other convenient place), then have your Windows Network Administrator remove the ability of each of the restricted users to map a network drive and the ability to open "My Network Places" from his workstation, then place the database file on the network in a "hidden share" directory. This will prevent the user from seeing the other computers on the network and their directories (specifically the directory where you want to hide your database), and thus accidently opening your database without your special shortcut. Computer-savvy users can still find ways around this, but it's unlikely most users will figure out how to do so. This method allows multiple users to open the database at the same time in "shared-mode."
- Apply "read-only" permissions to the database file for restricted users -- All database users must have "Full Control" permissions on the directory which contains the database, but you can also apply read-only permissions to the database file itself for the restricted users' Windows user accounts to prevent them from opening the database in anything but read-only mode.
This method may require lots of administrative maintenance, because every time the database is compacted, Access creates a brand new file, copies all objects from the original database file to the new file, deletes the original file, then renames the new file with the original file name. This new file doesn't necessarily have the same file permissions assigned to it that the original file had. Depending upon the version of Access, the operating system in use, and the "inheritance" permissions set on the directory by the Windows System Administrator, there's a wide range of different permissions that could be assigned to this new file, anywhere from no permissions to "Full-Control" permissions. You could easily find that the Windows System Administrator must reassign permissions on the new database file for all of the restricted users every time the database is compacted.
This method allows multiple users to open the database at the same time in "shared-mode" until the database is compacted, when the restricted users could be reassigned new permissions that would prevent this. Even if you test this scenario and it works in your organization's current I.T. environment, be aware that the next upgrade to the operating system or to Microsoft Office will likely wipe out your current "perfect plan" to ensure reassignment of read-only permissions to the database file for the restricted users after compaction. Because of the downsides to this approach, I don't recommend it.
- Botch the job -- Give one or more of the database users less than "Full Control" permissions on the directory which contains the database. This will cause problems every time one or more of the users can't lock the records in the database, due to the inability to manipulate the locking database file (*.LDB). Tell your boss, "I don't know what I'm doing." Your boss will understand.
When your boss says, "I can't get into the database. The message says, 'The file is already in use,'" explain to your boss, "That's because the first user to open the database didn't have 'create' permissions to create the locking database file, because I didn't give him 'Full Control' permissions on the directory that contains the database, because I don't know what I'm doing." Your boss will understand.
When your boss says, "I can't get into the database. The message says, 'Could not lock file,'" explain to your boss, "That's because you don't have 'modify' permissions to add your user information to the locking database file, because I didn't give you 'Full Control' permissions on the directory that contains the database, because I don't know what I'm doing." Your boss will understand.
When your boss says, "I can't make changes to the database. It only opens in 'read-only' mode," explain to your boss, "That's because you don't have 'modify' permissions to modify the database file, because I didn't give you 'Full Control' permissions on the database file, nor the directory that contains the database file, because I don't know what I'm doing." Your boss will understand.
Almost all bosses are great to work for, but there is one boss in the world who does not understand an employee who makes easily avoidable mistakes. Just in case you have the misfortune of working for that one boss in the entire world, then Door #6 is not for you. Please select one of the other five choices listed above for your multiuser database, all of which have the requirement that all database users must have "Full Control" permissions on the directory which contains the database. If none of these other choices suits your organization's I.T. environment, then your Microsoft Access database will be a single-user or read-only database much of the time and possibly a multiuser database part of the time (this will only happen when all of the current users have "Full Control" permissions on the directory). You won't be able to use the multiuser functionality of your Access database for all of your users all of the time.
Beware the "Gotcha!" -- Even when you have set up all of the database users with "Full-Control" permissions on the directory, it's possible that some of them may receive messages that the database is being opened read-only or the file has a sharing violation, or "The file is locked," or even "Access is denied." (Your boss may think that you picked Door #6!)
This can happen when:
- The client computer (the user's workstation) operating system is Windows XP SP-1 and the network server where the database is located is a Windows 2000-based server with Server Message Block (SMB) turned on; or
- The client computer has anti-virus software with network scanning turned on.
Please see Microsoft Knowledge Base Article 814112 for more information.
Also, if this multiuser database isn't already split into a front-end and back-end database, then you should split it and place the back-end on the shared network server and a copy of the front-end on each user's workstation. This will help prevent database corruption as well as give you several other benefits. For more details, see the Split the Database tip.
You can read about your favorite expert contributors.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
 Top
Visitors since 8 Nov. '04:
|
