Previous chapters discussed several aspects of database programming, focusing mainly on developing an application that would be used by a single user on a stand-alone PC. However, many of today's database applications must be written for a network environment, where multiple users will be reading, modifying, deleting, and adding to the data in the database. This presents an additional set of challenges for the database developer.
The main considerations that are involved in multiuser program development are the following:
Even if you don't develop applications for a network environment, you still need to be aware of some of the multiuser considerations. In Windows or any other multitasking environment, it is possible for two programs on the same machine to try to access the same data. As an example, consider a PC monitoring a manufacturing process. You might have one program receiving the process data from instruments and storing the data in a database. You would then have another program for generating reports on the data, or modifying erroneous or abnormal data points. Although both programs may be run by the same user on the same machine, they appear to the database to be multiple users of the data.
Determining the multiuser needs of the application is part of the design process. And, as with other aspects of programming, a good design will help tremendously in producing a good and efficient application.
In this chapter, you learn about the following:
Controlling data access involves placing restrictions on part or all of a database. Data access restrictions may be put in place as either user restrictions or function restrictions.
You need user restrictions when you want to prevent certain people (or, as a corollary, allow only certain people) from looking at sensitive information. An example would be a payroll system, where most people would be allowed to view the names of employees, but only a select few would be able to see or modify the actual pay information. These restrictions are usually handled through user IDs and passwords, and are the basis of data security.
Function restrictions, on the other hand, place limits on specific parts of a program, regardless of who the user is. An example of this would be opening a price table in read-only mode in an order-entry system. This would be done so that a user could not inadvertently change the price of an item while processing an order.
There are two ways of handling the restrictions in an application: programmatic controls and database engine controls. A programmatic control is one that the developer puts into the application itself. Engine-level controls restrict any program trying to access the information in the database.
The most restrictive limit that can be placed on a database is to open it exclusively. This prevents any other user or program from gaining access to any information in the database while it is in use. Because this method is so restrictive, it should be used only for operations that affect the entire database. These operations include
Within a program, you can open a database exclusively using the options portion of the OpenDatabase functions, as shown in the following code.
If the database is not in use, the database will be opened and no one else will be able to access it until it is closed. If the database is in use, an error will be returned. (Handling errors is discussed later in this chapter in the section “Handling Errors and Conflicts.”)
A less restrictive form of locking part of a database is to deny other users or programs access to the table in use by your program function. You can do this using the options of the OpenRecordset method to deny read and/or write access to the information with which you will be working. Similarly, you can deny write access to the information in a dynaset using the options of the OpenRecordset method.
When you use the deny options on a Recordset, it locks out other users from the base tables used to create the dynaset.
As with exclusive access, these options should be used only for administrative functions, when you don't want others viewing or updating information during the process.
The dbDenyRead option for the OpenRecordset method prevents other users from looking at the data in the affected table until you close the table. You would use this option if you needed to update information in the entire table, such as a global price increase. The following code shows the use of this option:
The dbDenyRead option is available only for table type recordsets. It cannot be used with dynasets or snapshots.
The dbDenyWrite option used in the OpenRecordset methods also restricts other users' access to information. In this case, however, the user may view but not update information in the affected table(s). Again, other users' access is restricted only until you close the table or dynaset. You might use the dbDenyWrite option if you are inserting new records into a table, but not making changes to existing records. The dbDenyWrite option is available for both table and dynaset type recordsets. Listing 7.1 shows the use of the dbDenyWrite option for the two functions.
Listing 7.1 Use dbDenyWrite to Prevent Others from Updating Tables While You Are Working with Them
Using the deny options does restrict other users' access to information in the database, but only if they open a table while you are using it with one of the options in effect. There will often be functions in your applications that have data you do not want the user to be able to modify. There will also be some tables that you do not want modified except by certain people. In these cases, you can open a table or dynaset as a read-only recordset, or you can use a snapshot.
One example of read-only tables is a lookup table. A lookup table contains reference information that is necessary for the user to see, but that the user does not need to change. For instance, your application might use a ZIP code table for a mailing list application or a price table for an order-entry system. In either of these cases, you would open the table in read-only mode using the options as shown in listing 7.2. Unlike the deny options, the read-only option does not restrict other users' access to the information.
Listing 7.2—Use the Read-Only Option to Prevent a User from Modifying Data
Another way to restrict a program function to read-only is to use a snapshot for the recordset. snapshots are always read-only. A snapshot can be used when data in the base tables is not being frequently changed by others, or when a point-in-time look at the data is sufficient. snapshots are usually used for reporting functions. An advantage to using snapshots is that they are stored in memory. Therefore, some operations using snapshots are faster than the same operations using tables or dynasets. However, because of the memory requirements for a snapshot and the time that it takes to load the data into memory, snapshots are best used for queries that return fewer than 200 records.
Finally, you may have occasion to want to restrict certain users to read-only access, no matter what program functions they are performing. This can be done only through the Jet security system. These security features are described later in this chapter in the section “Jet Security Features.”
The features described in the preceding section place restrictions on an entire table or even the entire database. These features are useful in multiuser programming, but are often too restrictive for some aspects of an application. One of the biggest considerations in multiuser programming is assuring that a record is not in use by another user at the same time that you are trying to update it. This is done through the use of record locks. A record lock temporarily limits the access of other users to a specific record or group of records.
In a typical application, a record lock is set while a user updates the data in the record, and then is released after the update is completed. The developer must take into account several considerations in the use of record locks. These are the following:
How you handle these considerations will have an impact on many aspects of the application development. Therefore, you should address these as much as possible in the design phase of the application.
The Jet engine does not support true record-locking. In record-locking, only the individual record currently being accessed by the user is locked. Instead, Jet uses a page-locking scheme. Jet reads data in pages of 2K (2048 bytes). When it places a lock on a record, it locks the entire page containing the record.
What this means to the developer and users is that multiple records are locked each time a lock is issued. The number of records locked depends on the size of each record. For example, each record in the customer table of the sample database is 230 bytes long. This would mean that nine records would be locked each time. On the other hand, the sales table has records that are only 30 bytes long, so each record lock would affect 68 records.
When a page is locked by one user, another user cannot modify any records on that page (although the other user can read the records), although the first user is working with only one of the records. This aspect of page-locking requires you to be even more careful in the application of record locks because it increases the chances of a conflict between users.
Visual Basic has no commands to specifically request a record lock. Instead, the record locks are automatically created and released when the Edit and Update methods are used. Visual Basic supports two locking methods: pessimistic and optimistic.
Pessimistic locking locks the page containing a record as soon as the Edit method is used on that record. The lock on the page is released when the Update method is used and the data is written to the file. The advantage of this method is that it prevents other users from changing the data in a record while you are editing it. The disadvantage is that it keeps the record locked for a longer period of time. In the worst case, a user could open a record for editing, placing a lock on it, then head out to lunch. This could keep other users from editing that record, or any others on the same page, for a long time.
Optimistic locking locks the page containing a record only when the Update method is invoked. The lock on the page is immediately released when the update operation is completed. The advantage of optimistic locking is that the lock is on the page for only a short period of time, reducing the chance that another user may try to access the same data page while the lock is in place. The disadvantage is that it is possible for another user to change the data in the record between the time the Edit and Update methods are used. If the data has changed in that time period, VB will issue an error message.
For most database applications, optimistic locking is the better choice of the two methods. The probability that someone else will change or delete the record you are working on is less than the probability that someone will try to access a record on the page that you have locked. If, however, you have an application where many users are accessing and editing records simultaneously, you may want to use pessimistic locking to ensure that the record is not changed while you are performing your edits. In this case, you will want to put some method in place to limit the time that the record is locked.
Pessimistic locking is the default method used by Visual Basic. To set the method of record-locking, you must set the Lockedits property of the table or dynaset with which you are working. Setting the property to True gives you pessimistic locking. Setting the property to False yields optimistic locking. Listing 7.3 shows how to set the Lockedits property for pessimistic and optimistic locking, respectively.
Listing 7.3—Set the Recordset's Lockedits Property to Choose the Record-Locking Method
As stated previously, the record locks are released automatically when the Update method has completed. However, releasing record locks is a background process, and there are times when other activities are occurring so rapidly that the database does not have time to catch up. If you are developing a data-entry-intensive program, you may need to pause the processing in the application momentarily. You can do this with the Idle method of the database engine.
The Idle method pauses the application and allows the database engine to catch up on its housekeeping work. The following line shows the syntax of the Idle method:
Since the data control uses tables and/or dynasets (the default) as its record source, the same locking schemes mentioned previously are used with the data control. Pessimistic locking is the default; therefore, as each record is accessed, the data control automatically performs the Edit method, which in turn automatically locks the record's page. When you move from one record to another, the lock on the current record is released by the Update method, and a lock is placed on the next record by the Edit method. In a multiuser system where you want to use optimistic locking, you will need to change the locking scheme of the data control. You do this by adding a LockEdits statement (see listing 7.3) to the Activate event of the form containing the data control.
You must be careful when using transactions in a multiuser environment. This is because any record locks that are set by the Edit or Update method are not released until the transaction is committed or rolled back. Therefore, it is best to keep transactions as short as possible to avoid a large number of records being locked for a long period of time. In addition, you should be careful when using cascaded updates or deletes, because these create more transactions and therefore more locks.
Another consideration of multiuser database programming is database security. Since a network environment may allow other people access to your database file, you may want to use methods to prevent them from viewing specific information in your database, or possibly prevent them from viewing any of the information.
The Jet engine provides a database security model based on user IDs and passwords. In this model, you may assign to individual users or groups of users permissions to the entire database, or any parts of the database. As each user is added to the security file, you must assign him to one or more user groups. He then inherits the permissions of that group. In addition, you may assign other permissions to the user.
If you are working with a secured database, you must do three things to gain access to the database from your VB program. These are the following:
The syntaxes of these statements are shown in listing 7.4.
Listing 7.4—To Gain Access to a Secured Database, You Must Specify the Location of the System Database, and Include the User ID and Password in Your CreateWorkspace Method
Within the Jet security system, there are two database-level permissions that may be set. These are Run/Open and Open Exclusive. The Run/Open permission is required for anyone who needs access to the database. Without it, a user cannot open a database for any function. The Open Exclusive permission lets a user open the database exclusively. This permission should be given only to administrative users. Otherwise, another user of an application may inadvertently lock the entire database.
Although database permissions affect the entire database (and every table in it), you will often need finer control over the access of users to individual tables. The Jet engine allows you to set table-level permissions for any table in a database. As with the database permissions, the table permissions can be assigned to individual users or groups of users. There are seven table-level permissions available with the Jet engine. These are the following:
The read and modify design permissions allow the user to work with the structure of the table. The administer permission gives a user full access to a table, including table-deletion capabilities. The four data permissions control the type of access a user has to the actual data in the table. These permissions may be assigned by table, and different users may be granted different access rights to each table.
Visual Basic has no means of creating the system database file (usually SYSTEM.MDA) that is needed for the security system. This file can only be created using Microsoft Access. Access also provides the easiest means of establishing and modifying user IDs and setting database and table permissions. However, from Visual Basic, you can create new user IDs, assign users to existing groups, and delete users as described in the following list:
Each of these activities is shown in listing 7.5.
Listing 7.5—You Can Perform Some Security System Maintenance Using Commands from Visual Basic
In addition to the security system, the Jet engine provides a means of encrypting a database that you create. Encryption is a method of disguising the data in a database so that someone using a disk-editing program cannot view the contents of the database. Encryption may be specified when the database is first created using the options portion of the CreateDatabase function. After a database has been created, encryption may be added or removed using the CompactDatabase function. The use of these functions for encrypting data is shown in listing 7.6.
Listing 7.6—You Can Add Encryption to Your Database Using the CreateDatabase or CompactDatabase Statement
The encryption method used by the Jet engine encrypts the entire database, including table definitions and queries. Also, the encryption results in a performance degradation of about 10 to 15 percent.
For some applications, it may be desirable to encrypt only a portion of the data. For instance, in a payroll system, you may need to encrypt only the actual pay rates, not the entire database. Although there is no built-in way to do this, you can create your own encryption schemes for these situations.
As an example, a simple encryption scheme for numeric data would be to convert each digit (including leading and trailing zeros) to a character, inverting the character string, then storing the data as text. In this way, the number 2534.75 could be stored as EGDCEB. Although this type of encryption is by no means foolproof, it does provide some data security from casual lookers.
In addition to, or in place of, the security built into the database, you may also choose to put a user ID and password system into your application. With an application-level system, you control the type of access people have to the functions of your application. The drawback to this approach is that someone could access your database using another program.
Finally, most network operating systems have their own security system built in. Many of these systems are quite good and can prevent unauthorized users from even knowing that the database exists. To determine the capabilities of your network's security system, refer to your network program manuals or contact your network administrator.
Currency of the data is a big issue in multiuser applications, especially those that handle a high volume of data entry and modification. Maintaining currency refers to making sure that the data at which you are looking is the most up-to-date information available. The data you are working with become non-current if another user changes or deletes the records since you retrieved them. Additionally, your recordset may be noncurrent if other users have added records since you retrieved data.
The only way to be sure that your data is always the most current is to work exclusively with tables. Only a table will immediately reflect changes, additions, or deletions made by other users. If your application or function works with only one table, using the table instead of a dynaset is probably the best way to go. If your application must work with multiple tables, the drawback to using just the tables is that you have to maintain the table relationships instead of using a dynaset to do it. To decide whether to use tables or dynasets, you must determine the probability that your data will not be current, the consequences of having noncurrent data, and the effort involved in maintaining the table relationships. Weighing these three factors will let you decide which access method is best.
If you need to work with a dynaset in a multiuser application, you can use the Requery method to make the dynaset current with the database. The Requery method basically re-executes the SQL query used to create the dynaset. Here is the Requery method:
There is a limit to the number of times that you can requery a dynaset. Therefore, it is a good idea after several requeries to close the dynaset and recreate it completely.
The performance of your multiuser application is dependent on, among other things, the type of network, the number of users, and the size of the databases with which you are working. At best, with you as the only user attached to a server, the data-transfer rates across a network are five to ten times slower than from your local hard drive. This means that you have to work harder in a network environment to keep the performance of your application crisp. The following sections list some ideas for helping the performance of your application.
The trick to keeping your dynasets small is to make your queries as specific as possible. This allows you to avoid repeatedly reading data across the network as you move through the dynaset.
If you have a database that does not change, such as a ZIP code database, you could make a copy of the database on your local drive. This improves the speed of access during searches and queries. For other databases that might change only occasionally (such as a price database), you could consider making the changes at a time when no one else is using the database. That way, the data would always be static to the users of the system. In other words, do your data maintenance at night.
Because snapshots are a read-only copy of the data stored in memory, they access the network only when the snapshot is created. Therefore, if you don't need to make changes to the data, use a snapshot, but only if the recordset is small.
Each time an update is issued, data is written to the database, requiring a disk write—that is, unless transaction processing is used. All the updates between a BeginTrans and a CommitTrans are stored in memory until the transaction is committed. At that time, all the updates are processed at once. This cuts down on the amount of writes being performed across the network. However, you should be careful not to allow too many updates to stack up at one time because of the record-locking concerns described earlier.
In a multiuser application, errors are triggered when you attempt to open a table or update a record that is locked by another user. These errors can be trapped by your code and appropriate steps can be taken to either retry the operation or exit the application gracefully. You will look at these errors in three major groups:
The way to handle most errors that occur when trying to lock a table, database, or record is to wait for a few seconds, then try the operation again. Unless the other user that has the record locked maintains the lock for a long time, this method will work. In an interactive environment, I usually give the user the choice of retrying or aborting the operation.
Database or table locking errors occur when you try to access information that is currently locked or in use by another user. These errors occur either when you try to open the database or table, or when you try to lock them. When the errors occur, it is necessary to wait until the other user has released the lock or quit using the recordset. Table 7.1 lists the error numbers and when they occur.
Table 7.1 Locking Errors That Apply to Tables and Databases
| Error Number | Error Occurs When |
| 3008 | You attempt to open a table that is exclusively opened by another. |
| 3009 | You attempt to lock a table that is in use by another. |
| 3211 | Same as 3009 |
| 3212 | Same as 3009 |
Each of these errors may be handled as described previously, with a choice by the user to abort or retry the operation.
Record-locking errors occur when you try to add, update, or delete records on a page locked by another user. Depending on the type of locking you use, the error may occur either when you use the Edit method (pessimistic locking) or when you use the Update method (optimistic locking). To determine which locking method is in effect when the error occurs, you can check the LockEdits property of the recordset you are attempting to lock. Then, if you choose to retry the operation, you can re-execute the correct method. This routine is shown in listing 7.7.
Listing 7.7 Determine Which Locking Method Is in Effect When an Error Occurs
Most of the record errors pertain to problems encountered while locking the record. However, one error requires special handling. This error (3197) occurs when a user attempts to update a record that has already been changed by another user. This error will occur only when optimistic locking is in effect. When it occurs, you need to present your user with the choices of "Make the new changes anyway" or "Keep the changes made by the other user." It would also be beneficial to show what the other user's changes were. If the user decides to make the changes anyway, the Update method may be executed a second time to make the changes.
Several other errors might occur when you attempt to lock a record. Table 7.2 lists the error numbers for these errors and when they occur.
Table 7.2 Other Record-Locking Errors
| Error Number | Cause |
| 3046 | You attempt to save a record locked by another user. |
| 3158 | You attempt to save a record locked by another user. |
| 3186 | You attempt to save a record locked by another user, but give the name of the user who placed the lock. |
| 3187 | You attempt to read a record locked by another user. |
| 3188 | You attempt to update a record that another program on your machine already has locked. |
| 3189 | You attempt to access a table that another user has exclusively locked. |
| 3218 | You attempt to update a locked record. |
| 3260 | You attempt to save a record locked by another user, but give the name of the user who placed the lock. |
The other major group of errors is permission errors. These errors occur when the Jet security is in operation and the current user does not have the appropriate permission to perform the operation. The only way to handle these errors is to inform the user of the error and abort the operation. Table 7.3 summarizes the permission errors.
Table 7.3 Permission Errors Occur When a User Does Not Have the Appropriate Rights for an Operation
| Error Number | Permission Required |
| 3107 | Insert |
| 3108 | Replace |
| 3109 | Delete |
| 3110 | Read definitions |
| 3111 | Create |
| 3112 | Read |
As you can see, there are many more design considerations involved in creating a multiuser application than in a single-user application. This is made even more difficult by the fact that each multiuser situation is different, in terms of hardware and network software used, the number of users of the system, and the functional requirements of the individual application. The intent of this section was not to provide specific solutions, but to make you aware of the challenges involved in multiuser programming and some of the tools available in Visual Basic to help you meet the challenges. Refer to the following chapters for more information:
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.