Platinum Edition Using Visual Basic 5

Previous chapterNext chapterContents


- 34 -
Multiuser Databases

For many programs, you want users to be able to access only certain parts of the database.
Jet has built-in security that you can use to prevent users from accessing sensitive information.
Record-locking schemes keep multiple users from changing the same record at the same time.
Many applications slow down when they are distributed across a network, but there are ways you can enhance performance.
Because database errors can occur, you need to know what they are so you can deal with them.
Using replication places the burden of keeping multiple copies of the data on the database engine.

In preceding chapters, you learned several aspects of database programming, particularly how to develop 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 type of application presents an additional set of challenges for you as a database developer.


NOTE: The examples in this chapter are tailored to a PC-level database, specifically the Jet database engine. A client/server environment, such as SQL Server, would handle all of these issues in a different (and usually better) manner.

In addition to multiple users accessing a single database, we will also look at how the multi-user concept works if each user has a separate copy of the database. The Jet engine has special database replication features designed to handle this.

The main considerations involved in multi-user program development are the following:

Even if you don't develop applications for a network environment, you still need to be aware of some multiuser considerations. In Windows or any other multitasking environment, two programs on the same machine can try to access the same data. As an example, consider a PC monitoring a manufacturing process. One program can receive the process data from instruments and store the data in a database. Another program can then generate reports on the data or modify erroneous or abnormal data points. Although the same user can run both programs on the same machine, the two programs 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 helps tremendously in producing a good and efficient application.

Controlling Data Access

Controlling data access involves placing restrictions on part or all of a database. Data access restrictions can 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, to allow only certain people) from looking at sensitive information. An example is a payroll system, in which most people can view the names of employees, but only a select few can 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 is opening a price table in read-only mode in an order-entry system. You add function restrictions so that a user cannot inadvertently change the price of an item while processing an order.

You can handle the restrictions in an application in two ways: using programmatic controls or using database engine controls. A programmatic control is one that you put into the application itself. Engine-level controls restrict any program trying to access the information in the database.

Using a Database Exclusively

The most restrictive limit that you can place on a database is to open it exclusively. This limit 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, you should use it only for operations that affect the entire database. These operations include the following:

Within a program, you can open a database exclusively using the options portion of the Data Access Object OpenDatabase method, as shown in the following code:

Dim db  As Database
Set db = DBEngine.Workspaces(0).OpenDatabase("D:\VB5\BIBLIO.MDB", True, False)

If the database is not in use, it opens and no one else can access it until it is closed again. If the database is in use, an error is returned. (Handling errors is discussed later in this chapter in the section "Handling Errors and Conflicts.")

Denying Table Access to Others

In addition to opening the database exclusively, you can also open recordsets exclusively. This is a less restrictive way to lock part of a database. When you create a recordset, you can deny other users or programs access to the table being used by your program function. You can do so by using the options of the OpenRecordset method to deny read or write access to the information with which you will be working. Similarly, you can deny write access to the information in a dynaset-type recordset by using the options of the OpenRecordset method.


CAUTION: When you use the Deny options on a recordset created from multiple tables, other users are restricted from the base tables used to create the recordset.

You should use these options, as with exclusive access, only for administrative functions, when you don't want others viewing or updating any of the table's information during the process.

Using the Deny Read Option (dbDenyRead) 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 use this option if you need to update information in the entire table, such as a global price increase. The following code shows the use of this option:

Dim tblMain As Recordset
Set tblMain = db.OpenRecordset("Titles", dbOpenTable, dbDenyRead)


NOTE: The dbDenyRead option is available only for table type recordsets. You cannot use it with dynasets or snapshots.

Using the Deny Write Option (dbDenyWrite) The dbDenyWrite option used in the OpenRecordset method also restricts other users' access to information. In this case, however, the users can view but not update information in the affected table or tables. Again, other users' access is restricted only until you close the recordset. You might use the dbDenyWrite option if you're 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 34.1 shows the use of the dbDenyWrite option for the two functions.

Listing 34.1 Use dbDenyWrite to Prevent Others from Updating Tables While You Are Working with Them

Dim tblTitles As Recordset
Dim rsAuthors As Recordset
Dim sSQL As String
` Open a table with the dbDenyWrite option.
Set tblTitles = db.OpenRecordset("Titles", dbOpenTable, dbDenyWrite)
` Create a Recordset with the dbDenyWrite option.
sSQL = "Select * From Authors"
Set rsAuthors = db.OpenRecordset(sSQL, dbOpenDynaset, dbDenyWrite)

Using the deny options does not restrict other users' access to information in the database all the time. They are denied access only if they attempt to open a table while you are using it with one of the options in effect. In other words, this is not really a security feature but a way to manage multiple updates to the database.

Using Read-Only Tables

Often you might have functions in your applications that have data you don't want the users to be able to modify at any time. You might also have some tables that you want only certain people to modify. In these cases, you can open a table or recordset in read-only mode, or you can use a snapshot-type recordset.

Using Lookup Tables One example of a read-only table is a lookup table. A lookup table contains reference information that is necessary for the users to see but that the users do 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 open the table in read-only mode using the options shown in Listing 34.2. Unlike the deny options, the read-only option does not restrict other users' access to the information.

Listing 34.2 Use the Read-Only Option to Prevent Users from Modifying Data

Dim rsPubs As Recordset
Set rsPubs = db.OpenRecordset("Publishers", dbOpenTable, dbReadOnly)


NOTE: You can open an entire database in read-only mode by setting the ReadOnly parameter of the OpenDatabase method to True.

Using Snapshots Another way to restrict a program function to read-only is to use a snapshot-type recordset. Jet engine snapshots are always read-only. You can use a snapshot when data in the base tables is not being changed frequently 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.

Restricting Specific Users Finally, you might have occasion to want to restrict certain users to read-only access, no matter what program functions they are performing. You can do so only through the Jet security system. These security features are described later in this chapter in the section "Exploring Jet Security Features."

Understanding Record-Locking Schemes

The features described in the preceding section place restrictions on an entire table or even the entire database. In this section, we discuss locking the database at the record level. One of the main 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. You do so 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. As the developer, you must take into account the following considerations in the use of record locks:

How you handle these considerations has an impact on many aspects of the application development. Therefore, you should address these issues as much as possible in the design phase of the application.

Page-Locking versus Record-Locking

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 (2,048 bytes). When it places a lock on a record, it locks the entire page containing the record.

In this locking scheme, multiple records are locked each time a lock is issued. The number of records locked depends on the size of each record. For example, suppose each record in a sample table is 230 bytes long. Nine records therefore are locked each time. On the other hand, if the table has records that are only 30 bytes long, each record lock affects 68 records.

When a page is locked by one user, another user cannot modify any records on that page (although the second user can read the records). This is true even if 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 Add, Edit, and Update methods are used. Visual Basic supports two locking schemes: pessimistic and optimistic.

Pessimistic Locking

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 approach is that it prevents other users from changing the data in a record while one user is 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, place a lock on it, and then head out to lunch. This lock would keep other users from editing that record, or any others on the same page, for a long time.


NOTE: To prevent locks from being held too long, you can put a timer in your code that releases the record after a specified period of inactivity. You would do this by placing code in the Timer event of the Timer control. This code would use the Idle method of the database engine as shown in the following line of code:



DBEngine.Idle dbFreeLocks



Optimistic Locking

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 might try to access the same data page while the lock is in place. The disadvantage is that another user can 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 issues an error message.

Which Locking Method to Use and When

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 in which many users are accessing and editing records simultaneously, you might want to use pessimistic locking to ensure that the record is not changed while you are performing your edits. In this case, you should put some method in place to limit the time that the record is locked.

Pessimistic locking is the default record-locking scheme 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 34.3 shows how to set the LockEdits property for pessimistic and optimistic locking, respectively.

Listing 34.3 Set the Recordset's LockEdits Property to Choose How Record-Locking Works

Dim rsTemp As Recordset
`Set the locking method to pessimistic
rsTemp.LockEdits = True
`Set the locking method to optimistic
rsTemp.LockEdits = False

Releasing Locks

As stated previously, the record locks are released automatically when the Update method has completed. However, releasing record locks is a background process, and sometimes 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 might need to pause the processing in the application momentarily. You can do so by using 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:

DBEngine.Idle dbFreeLocks

Using the Data Control

Because the data control uses tables 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 in which you want to use optimistic locking, you need to change the locking scheme of the data control. You do so by adding a LockEdits statement, as shown in Listing 34.3, to the Activate event of the form containing the data control.


CAUTION: You must be careful when using transactions in a multiuser environment. Any record locks that are set by the Edit or Update method are not released until the transaction is committed or rolled back. Therefore, keeping transactions as short as possible is best so that you can avoid having a large number of records locked for a long period of time. In addition, you should be careful when using cascaded updates or deletes because they create more transactions and, therefore, more locks.

Exploring Jet Security Features

Another consideration of multiuser database programming is database security. Because a network environment can allow other people access to your database file, you might 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 can 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 or her to one or more user groups. That user then inherits the permissions of that group. In addition, you can assign other permissions to the user.

If you're working with a secured database, you must perform the following three steps to gain access to the database from your VB program:

The syntax for each of these statements is shown in Listing 34.4.

Listing 34.4 Gaining Access to a Secured Database

Dim wsTemp As Workspace
           DBEngine.SystemDB = "C:\Windows\System\System.mdw"
  Set wsTemp = DBEngine.CreateWorkspace("MYWSPACE", "MyName", "MyPassword", _            dbUseJet)

For the preceding code to work, the user name and password must be set in the system database first. To do this, read the section "Setting Up the Security System" later in this chapter. By default, the SYSTEM.MDW contains an administrator account whose username is admin with no password.

Database Permissions

Within the Jet security system, you can set two database-level permissions: Run/Open and Open Exclusive. The Run/Open permission is required for anyone who needs access to the database. Without this permission, a user cannot open a database for any function. The Open Exclusive permission allows users to open a database exclusively, as described previously. You should give this permission only to administrative users. Otherwise, another user of an application might inadvertently lock everyone else out of the database until he closes it.

Table Permissions

Although database permissions affect the entire database (and every table in it), you often need finer control over access to individual tables. Using the Jet engine, you can 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. The following seven table-level permissions are available with the Jet engine:

With the Read and Modify Design permissions, the user can 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. You can assign these permissions by table, and you can grant different users different access rights to each table. For the constants used to set permissions, see "Permissions Property" in the help file.

Setting Up the Security System

Visual Basic has no means of creating the system database file needed for the security system. You can create this file using only Microsoft Access. Access also provides the easiest means of establishing and modifying user IDs and setting database and table permissions. However, after the file exists, you can use VB code to 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 34.5.

Listing 34.5 Performing Security System Maintenance from Visual Basic

  Dim wsTemp As Workspace
  Dim NewUser As User
  Dim NewGrp As Group
    
  `Add a new user to the system database
  DBEngine.SystemDB = "C:\Windows\System\System.mdw"
  Set wsTemp = DBEngine.Workspaces(0)
  Set NewUser = wsTemp.CreateUser("BSILER", "12345", "PASSWORD")
 
  `Add the user to the "Users" group
  wsTemp.Groups("Users").Users.Append NewUser
  `Delete the user from the system database
wsTemp.Users.Delete "BSILER"

Encryption

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. You can specify encryption when first creating the database by using the options portion of the CreateDatabase function. After a database has been created, you can add or remove encryption by using the CompactDatabase function. The use of these functions for encrypting data is shown in Listing 34.6.

Listing 34.6 Adding Encryption to Your Database

`Create an encrypted database
    Dim dbNew As Database
    Set dbNew = DBEngine.Workspaces(0).CreateDatabase("D:\TEST.MDB",_   
         dbLangGeneral, dbEncrypt)
    `Encrypt an existing database
    DBEngine.CompactDatabase "D:\TEST.MDB", "D:\TEST2.MDB", , dbEncrypt
    `Remove encryption from a database
  DBEngine.CompactDatabase "D:\TEST2.MDB", "D:\TEST3.MDB", , dbDecrypt

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, you might want to encrypt only a portion of the data. For instance, in a payroll system, you might need to encrypt only the actual pay rates, not the entire database. Although no built-in method is available for this type of encryption, you can create your own encryption schemes for these situations.

As an example, a simple encryption scheme for numeric data is to convert each digit (including leading and trailing zeroes) to a character, invert the character string, and then store the data as text. In this way, the number 2534.75 can be stored as EGDCEB. Although this type of encryption is by no means foolproof, it does provide some data security from casual lookers.

Application Passwords

In addition to, or in place of, the security built into the database, you also can 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 by using another program. However, this type of security is fairly easy to implement. Visual Basic even includes a form template for a login box.

Using Network Security

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. For example, the path on the file server containing the database could simply be restricted from unauthorized users. This might be the easiest way to secure your database, because it would involve no programming. To determine the capabilities of your network's security system, refer to your network program manuals or contact your network admini-strator.

Maintaining Data Currency

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're working with becomes noncurrent if another user changes or deletes the records since you retrieved them. Additionally, your recordset might be noncurrent if other users have added records since you retrieved data.

Using Only Tables

The only way to be sure that your data is always the most current is to work exclusively with tables. Only a table immediately reflects 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 help you decide which access method is best.

Requerying a Dynaset

If you need to work with a dynaset-type recordset in a multiuser application, you can use the Requery method to make it current with the database. The Requery method, shown here, basically re-executes the SQL query:

rsTemp.Requery 

You can requery a dynaset only a limited number of times. Therefore, after several requeries, you should close the dynaset and re-create it completely.


NOTE: Depending on how the recordset was created, the Requery method might or might not be available. You should check the Restartable property of the recordset to verify that it supports the Requery operation.

Probing Performance Considerations

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're working. At best, with you as the only user attached to a server, the data-transfer rates across a network are a lot 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. In the following sections, I list some ideas for helping the performance of your application.

Keep Recordsets Small

The trick to keeping your recordsets small is to make your queries as specific as possible. This way, you can avoid repeatedly reading data across the network as you move through the records. For example, consider the "Titles" table in the BIBLIO.MDB sample database. Suppose you wanted to print a list of all the titles in the table. One way to do this would be to open a recordset as follows:

sSQL = "Select * From Titles" Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
rs.MoveFirst
  While Not rs.EOF
      Print rs.Fields("Title")
      rs.MoveNext
Wend

However, note that the SQL statement brings back all the fields for each record. Because you only care about the Title field, a better SQL statement would be

sSQL = "Select Title From Titles"

Copy a Database or Table to a Local Drive

If you have a database that is used for lookup purposes, such as a ZIP Code database, you can make a copy of the database on your local drive. This approach improves the speed of access during searches and queries. For example, the following code copies a database to your drive if the network copy is newer:

   Dim sLocalMDB As String
   Dim sNetMDB As String
    
   sLocalMDB = "C:\ZIPCODES.MDB"
   sNetMDB = "\\MYSERVER\MYPATH\ZIPCODES.MDB"
    
   `Make sure your copy of the database
   `is closed before attempting this.
   If FileDateTime(sNetMDB) > FileDateTime(sLocalMDB) Then
       FileCopy sNetMDB, sLocalMDB
End If

For other databases that might change only occasionally (such as a price database), you might consider making the changes at a time when no one else is using the database. That way, the data is always static to the users of the system. In other words, do your data maintenance at night.

Use Snapshot-Type Recordsets Where Possible

Because snapshots are read-only copies of the data stored in memory, they access the network only when the snapshots are created. Therefore, if you don't need to make changes to the data, use a snapshot--but only if the recordset is small. A large snapshot can choke your memory and resources in a hurry.

Use Transactions for Processing Updates

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 approach 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.

Using Database Replication to Handle Multiple Users

Although database replication might not be multiuser in the strictest sense of the concept (multiple users accessing the same database at the same time), you can use this process to handle a number of situations in which multiple users need to work with a database. The most easily visualized of these situations is one in which a sales force in different locations works with a common database and then sends information back to a central site where summary reports are developed.

With database replication, each person can work with a copy of the data in a database. Then, at certain times, the individual databases are recombined with the master database. At the same time that the data from the replica databases is passed back to the master database, any structure changes made to the master can be passed to the replicas.

You also can use database replication to create a read-only copy of a database for a user. You do so to make a complete copy of a network database on a user's local machine. You can use this approach to speed up the processing of large reports or queries when the user has no need to modify the base information.

Managing database replication involves four basic steps:

1. Making a database replicable (that is, capable of being replicated)

2. Making copies (replicas) of the database

3. Periodically synchronizing the copies of the database

4. Handling synchronization conflicts


NOTE: Database replication features are available only for Jet version 3.0 or later databases.

Making a Replicable Database

To create a database that can be replicated, you must use a user-special, user-defined property of the database--the Replicable property.


NOTE: User-defined properties were first introduced in Visual Basic 4. Using these properties, you can add properties to a database, Querydef, table, index, or field object of your database. User-defined data objects are described in detail in Chapter 31, "Improving Data Access with Data Access Objects(DAO)."

Making a database replicable requires only a few lines of code and a single user-defined property, as shown in the following code example. This property is a text property with the name Replicable and a value of T:

Dim RepProp As Property
Set OldDb = OldWs.OpenDatabase(DataName, True)
Set RepProp = OldDb.CreateProperty()
RepProp.Name = "Replicable"
RepProp.Type = dbText
RepProp.Value = "T"
OldDb.Properties.Append RepProp
OldDb.Close


NOTE: A database must be opened exclusively in order to make it replicable.

Making Copies of the Database

After you change your database so that it can be replicated, you should make copies of the database to give to the various users. You do so by using the MakeReplica method of the database object, which is shown in the following code:

`(dbMaster is a database that has been opened previously)
dbMaster.MakeReplica "E:\REPLTEST\REPLICA2.MDB", "Copy Number 2"

With the MakeReplica method, you supply a name and description for the database copy. There also is a third optional parameter that indicates whether the copy should be read-only. If you want the copy to be read-only, use the constant dbRepMakeReadOnly as the third argument. Another constant, dbRepMakePartial, is used to copy only some of the data to the replica. In the sales force example, this would be useful to provide each mobile person with the information for their region only, which would then be synchronized back into the "master" database. The help file has details on how to determine which information goes into a partial replica.

Putting the Database Back Together

Finally, after making changes to the master database or one or more of the replicas, you should make the data and structure consistent between all the databases by using the Synchronize method. This method synchronizes the data between two databases. You define one of the databases as the database object, which is running the Synchronize method. You specify the other database (defined by its path and file name) as an argument of the method. The data exchange between the files can be one of the following three types as defined by the constants shown in parentheses:


NOTE: A fourth option, dbRepSyncInternet, is available in the Office 97 developer edition.

The Synchronize method is shown in the following code:

Set OldDb = OldWs.OpenDatabase(DataName)
OldDb.Synchronize "E:\REPLTEST\REPLICA2.MDB", dbRepImportChanges
OldDb.Close

Handling Errors and Conflicts

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. In the following sections, you 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 and then try the operation again. Unless the other user who 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- and Table-Locking Errors 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, you need to wait until the other user has released the lock or quit using the recordset. Table 34.1 lists the error numbers and when they occur.

Table 34.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 user.
3009 You attempt to lock a table that is in use by another user.
3211 Same as 3009
3212 Same as 3009, except that this error provides information about the user and machine using the table.

Each of these errors can be handled as described previously, with a choice by the user to abort or retry the operation.

Record-Locking Errors 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 can 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 by using the routine shown in Listing 34.7. Then, if you choose to retry the operation, you can re-execute the correct method.

Listing 34.7 Determine Which Locking Method Is in Effect When an Error Occurs

`****************************************
`Determine the type of locking being used
`****************************************
If NewDyn.LockEdits Then
`**********************************
`If pessimistic locking, retry Edit
`**********************************
  ewDyn.Edit
Else
`***********************************
`If optimistic locking, retry Update
`***********************************
  NewDyn.Update
End If

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 occurs 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." Showing the other user's changes also is beneficial. If the user decides to make the changes anyway, he or she can execute the Update method a second time to make the changes.

Several other errors might occur when you attempt to lock a record. Table 34.2 lists the error numbers for these errors and when they occur.

Table 34.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. The message gives 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. The message gives the name of the user who placed the lock.

Permission Errors 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 34.3 summarizes the permission errors.

Table 34.3 Permission Errors that Occur When a User Does Not Have the Appropriate Rights for an Operation

Error Number Permission Required
3107 Insert
3108 Update
3109 Delete
3110 Read Definitions
3111 Create
3112 Read

Handling Synchronization Conflicts One type of error occurs when you attempt to synchronize incompatible databases. When you use Jet's replication and synchronization features, you are essentially working with a set of databases--a master database and a bunch of replicas. When a replica is created with the MakeReplica method, it is given a unique ID. This ID is stored in the ReplicaID property of the database. Similarly, there is a property called DesignMasterID which identifies one of the databases as having the database design upon which all replicas are based. Together these IDs identify a replica set. If you attempt to synchronize across different sets, an error will occur. Other types of errors that occur are due more to data conflicts than errors in the synchronization process. In the sales force example, suppose there are only two salespeople, each with his own database replica. Let's say the database includes a table that lists customers along with a field called LastVisitDate, which represents the last time any salesperson visited the customer. If synchronization is performed after each visit, conflicts won't occur and the LastVisitDate field will be the same in both replicas. However, suppose our entire sales force visits the same customer and then attempts to synchronize their databases. Which salesman's LastVisitDate field will be propagated to both databases?

The answer is neither. Resolving conflicts like this must be handled by your program code. This makes sense, because Visual Basic cannot be expected to know that the later of the two dates is the logical choice for this database. What Jet does do, however, is give you all the information necessary to resolve a conflict. It does this by creating a new table, the conflict table, and placing the conflicting record(s) in it. Your program can then look at the records and make a decision to overwrite the existing one. In our fictional situation, we would simply take the later of the two dates.

Both types of synchronization errors discussed in the preceding can be avoided with better database design. For example, if our sales force of two had a third database located at the sales company, each salesperson could synchronize with that database rather than with each other.

From Here...

As you can see, many more design considerations are involved in creating a multiuser application than in a single-user application. This process 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 chapter 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 chapter for more information:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.