Previous Page TOC Index Next Page Home


18

Developing for a Multiuser Environment

Designing Your Application with Multiuser in Mind

When you develop applications that will be accessed over the network by multiple users, you must ensure that your applications effectively handle the sharing of data and other application objects. Many options are available for developers when they design multiuser applications. This chapter covers the pros and cons of these options.

Multiuser issues are the issues surrounding the locking of data. Multiuser issues include deciding where to store database objects, when to lock data, and how much data to lock. In a multiuser environment, having several users simultaneously attempting to modify the same data can generate conflicts. As a developer, you need to handle these conflicts. Otherwise, your users will experience unexplainable errors.

Multiuser Design Strategies

Numerous methodologies exist for handling concurrent access to data and other application objects by multiple users. Each of these methodologies introduces solutions as well as problems. It is important to select the best solution for your particular environment.

Strategies for the Installation of Access

There are two strategies for the installation of Access:

Each of these strategies has associated pros and cons. The advantages of running Access from a file server are as follows:

Although the advantages of installing Access on a file server might seem compelling, there are serious drawbacks to a file server installation, including the following:

Because the disadvantages of running Access from a file server are so pronounced, I strongly recommend that Access, or at least the Runtime, be installed on each user's machine.

Strategies for the Installation of Your Application

Just as there are different strategies for the installation of Access, there are also various strategies for the installation of your application, such as the following:

In other words, after you have created an application, you can place the entire application on the network, which means that all of the tables, queries, forms, reports, macros, and modules that make up the system reside on the file server. Although this method of shared access keeps everything in the same place, you will see many advantages to placing only the data tables in a database on the file server. The remainder of the objects are placed in a database on each user's machine. Each local application database is linked to the tables on the network. In this way, users share data but not the rest of the application objects.

The advantages of installing on the file server one database containing data tables and installing locally another database containing the other application objects are as follows:

In addition to storing the queries, forms, reports, macros, and modules that make up the application in a local database, I also recommend that you store the following objects within each local database:

Temporary tables should be stored in the database that is located on each workstation, because if two users are performing operations that build the same temporary tables, you do not want one user's process to interfere with the other user's process. The potential conflict of one user's temporary tables overwriting the other's can be eliminated by storing all temporary tables in each user's local copy of the database.

You should also place static lookup tables, such as a state table, on each workstation. Because the data does not change, maintenance is not an issue. The benefit is that Access does not need to pull that data over the network each time it is needed.

Semistatic tables can also be placed on the local machine. Semistatic tables are tables that are rarely updated. As with static tables, the major benefit of having these tables reside in a local database is that reduced network traffic means better performance not only for the user requiring the data, but also to anyone sharing the same network wire. Changes made to the semistatic tables can be transported to each workstation using Replication. Replication is covered in Chapter 23, "Replication Made Easy."

The configuration that has been described throughout this section is pictured in Figure 18.1.


Figure 18.1. An example of configuration with database objects split, storing temporary and static tables locally and shared tables remotely (on the file server).

The Basics of Linking to External Data

Linking to external data, including data that is not stored within another Access database, is covered extensively in Chapter 19, "Using External Data." Three options are available to you:

The first two options are covered in Chapter 19. The last option, the Database Splitter Wizard, is covered here. To split the objects within a database into two separate .MDB files, follow these steps:

  1. Open the database whose objects you want to split.

  2. Select Tools|Add-Ins|Database Splitter. The Database Splitter dialog, pictured in Figure 18.2, appears.


Figure 18.2. The Database Splitter dialog.

  1. Click Split Database. The Create Back-end Database dialog appears (see Figure 18.3).


Figure 18.3. The Create Back-end Database dialog.

  1. Enter the name for the database that will contain all of the tables. Click Split. The Database Splitter wizard creates a new database that contains all of the tables. Links are created between the current database and the database containing the tables. This can be seen in Figure 18.4.


Figure 18.4. The database that has been split.


Be aware that when distributing an application using linked tables, it is necessary to write code to ensure that the data tables can be located from each application database on the network. If each user has the same path to the file server, this is not a problem. If the path to the file server varies, you need to write a routine that checks to ensure that the tables can be located. If they cannot be located, the routine prompts the user for the location of the data. This routine is covered in Chapter 19.

Understanding Access's Locking Mechanisms

Although the preceding tips for designing network applications reduce network traffic, they in no way reduce locking conflicts. In order to protect shared data, Access locks a page of data as the user edits a record. When a page of records is locked, multiple users can read the data, but only one user can make changes to the data. Data can be locked through a form and also through a recordset that is not bound to a form.

Here are the three methods of locking for an Access application:

With Page locking, only the page containing the record that is being edited is locked. On the other hand, with Table and Recordset locking, the entire table or recordset containing the record that is being edited is locked. With Database locking, the entire database is locked, unless the user opening the database has opened it for read-only access. If the user opens the database for read-only access, other users can also open the database for read-only access. The ability to obtain exclusive use of a database can be restricted through security.

It is important to note that the locking scheme to which you must adhere depends upon the source of the data that you are accessing. If you are accessing database server data via ODBC, you inherit the locking scheme of the particular back-end that you are using. If you are accessing ISAM data over a network, you get any record locking that the particular ISAM database supports. For example, if you are accessing FoxPro data, you have the capability to utilize record locking or any other locking scheme that FoxPro supports.

Locking and Refreshing Strategies

Access provides several tools for controlling locking methods in datasheets, forms, and reports. To configure the global multiuser settings, select Tools|Options and then click on the Advanced tab. The dialog pictured in Figure 18.5 appears.


Figure 18.5. The Advanced Options dialog.

The following multiuser settings can be configured from this dialog:

Default Record Locking

The Default Record Locking option enables you to specify the default record locking as No Locks (optimistic), All Records (locks entire table or dynaset), or Edited Record (pessimistic). This is where you can affect settings for all objects in your database. Modifying this option will not affect any existing queries, forms, and reports, but it will affect any new queries, forms, and reports. These options are discussed later in this chapter as they apply to forms and recordsets.

Determining the Locking Mechanism for a Query

If you want to determine the locking method for a particular query, you can do this by modifying the Record Locks query property. Once again, the options are No Locks, All Records, and Edited Record. The Query Properties box can be seen in Figure 18.6.


Figure 18.6. The Query Properties window.

Determining the Locking Mechanism for a Form or Report

Just as you can configure the locking mechanism for a query, you can also configure the locking mechanism for each form and report. Forms and reports have Record Locks properties (see Figure 18.7). Changing these properties modifies the locking mechanism for that particular form or report.


Figure 18.7. The Form Properties window.


Reports do not provide the Edited Records choice for locking. The Edited Records option is not necessary because report data cannot be modified.

Default Open Mode

The Default Open Mode of the Advanced Options dialog enables you to configure the default open mode for databases. By encouraging users to set this option within their own copies of Access, you prevent people from inadvertently opening up a database exclusively. Take a good look at the Access File Open dialog (see Figure 18.8). Whether or not the Exclusive check box is selected is determined by the Default Open Mode set in the Advanced Options dialog.


Figure 18.8. The File Open dialog.

Number of Update Retries

The number of update retries is used to specify how many times Access will reattempt to save data to a locked record. The higher this number is, the larger the chance that the update will succeed. The down side is that the user has to wait while Access continues attempting to update the data, even when there is no hope that the update will complete successfully. The default for this setting is 2. The value can range from 0 to 10.

ODBC Refresh Interval

The ODBC refresh interval determines how often your form or datasheet is updated with changes made to data stored in ODBC datasources. For example, assume that two users are viewing the same data stored in a back-end Microsoft SQL server database. User 1 makes a change to the data. The ODBC refresh interval determines how long it will be before User 2 sees the change. The higher this number is, the less likely it is that User 2 will see the current data. The lower this number is, the more network traffic will be generated. The default for this setting is 500 seconds. The value can range from 1 to 3600 seconds.

Refresh Interval

The Refresh Interval is used to specify how long it takes for a form or datasheet to be updated with changed data from an Access database. This is very similar to the ODBC Refresh Interval, but the ODBC Refresh Interval applies only to ODBC datasources, and the Refresh Interval applies only to Access datasources. As with the ODBC Refresh Interval, the higher this number is, the lower the chance that the data seen by the user is current. The lower this number is, the more network traffic is generated. The default for this setting is 60 seconds. The value can range from 1 to 32,766 seconds.


Access automatically refreshes the data in a record whenever the user attempts to edit the record. The benefit of a shorter Refresh Interval is that the user sees that the record has been changed or locked by another user before the user attempts to edit it.

Update Retry Interval

The Update Retry Interval is used to determine how many seconds Access waits before once again attempting to update a locked record. The default for this setting is 250 milliseconds. The value can range from 0 to 1000 milliseconds.

Refreshing versus Requerying Data

It is important that you understand the difference between refreshing and requerying a recordset. The process of refreshing a recordset updates changed data and indicates any deleted records. The refresh process does not attempt to bring a new recordset over the network wire. Instead, it refreshes the data in the existing recordset. This means that records are not reordered, new records do not appear, and deleted records are not removed from the display. The record pointer remains on the same record.

The refresh process is quite different from the requery process. The requery process obtains a new set of records. This means that the query is run again and all of the resulting data is sent over the network wire. The data is reordered, new records appear, and deleted records are no longer displayed. The record pointer is moved to the first record in the recordset.

Form Locking Strategies

Earlier in the chapter, you learned about the locking strategies for forms. They are No Locks, All Records, and Edited Record. Utilizing the three locking strategies as appropriate, you can develop a multiuser application with little to no multiuser programming. You won't gain the same power, flexibility, and control that you get out of recordsets, but you can quickly and easily implement multiuser techniques. In this section, you will see how all three of these strategies impact the bound forms within your application.

No Locks

The No Locks option means that the page of data containing the edited record will not be locked until Access attempts to write the changed data to disk. This occurs when there is movement to a different record or the data within the record is explicitly saved. The No Locks locking option is the least restrictive of the three locking options for forms. Multiple users can be editing data within the same 2K page of data at the same time. The conflict occurs when two users attempt to modify the same record. Consider the following example: User 1 attempts to modify data within the record for customer ABCDE. User 2 attempts to modify the same record. No error occurs because the No Locks option is specified for the form that both users are accessing. User 1 makes a change to the address. User 2 makes a change to the Contact Title. User 1 moves off of the record, saving her changes. No error occurs because Access has no way of knowing that User 2 is modifying the record. Now User 2 attempts to move off of the record. The Write Conflict dialog, shown in Figure 18.9, appears. User 2 has the choice of saving her changes, thereby overwriting the changes that User 1 made; copying User 1's changes to the clipboard so that she can make an educated decision as to what to do; or dropping her own changes and accepting the changes that User 1 made.


Figure 18.9. The Write Conflict dialog.

All Records

The All Records locking option is the most restrictive. When All Records is in effect, other users can only view the data in the tables underlying the form. They cannot make any changes to the data, regardless of their own locking options. When opening the form, they receive a quick status bar message that the data is not updatable. If they attempt to modify data within the form, the computer beeps and a message is displayed in the status bar.

Edited Record

The Edited Record option is used when you want to prevent the conflicts that occur when the No Locks option is in place. Instead of getting potential conflicts regarding changed data, the users are much more likely to experience locking conflicts, because every time a user begins editing a record, the entire 2K page of data surrounding the record will be locked. Consider this scenario. User 1 begins editing a record. User 2 attempts to modify the same record. The computer beeps and a lock symbol appears in the form's record selector (see Figure 18.10). Now User 2 moves to another record. If the other record is in the same 2K page as the record that User 1 has locked, the locking symbol appears and User 2 is unable to edit that record as well until User 1 has saved the record that she was working on, thereby releasing the lock.


Figure 18.10. The lock symbol with edited record.


If you want to override any of the default locking error dialogs that appear when in a form, you must code the Error event of the form. Although you can use this method to replace any error message that appears, you cannot trap for the situation with Pessimistic locking when another user has the record locked. Users are only cued that the record is locked by viewing the locking symbol and hearing the beep that occurs when they attempt to edit the record. If you want to inform users that the record is locked before they attempt to edit it, you need to place code in the timer event of the form that checks to see whether the record is locked. Checking to see whether a record is locked is covered in the "Testing a Record for Locking Status" section of this chapter.

Recordset Locking

Recordset locking is the process of locking pages of data contained within a recordset. Using recordset locking, you can determine when and for how long the data is locked. This is different from locking data via bound forms. With bound forms, you have little control over the specifics of the locking process.

When you are traversing through a recordset, editing and updating data, locking occurs regardless of whether you intervene. It is important for you to understand when the locking occurs and whether you need to step in to intercept the default behavior.

If you do nothing, an entire page of records will be locked each time you issue an edit method from within your VBA code. This page is 2048 bytes (2K) in size and surrounds the record being edited. If an OLE object is contained within the record being edited, it is not locked with the record because it occupies its own space.

Pessimistic Locking

VBA enables you to determine when and for how long a page is locked. The default behavior is called pessimistic locking. This means that the page is locked when the edit method is issued. Here is some sample code that illustrates this process:

Sub PessimisticLock(strCustID As String)
    Dim db As Database
    Dim rst As Recordset
    Dim strCriteria As String
    Set db = CurrentDb()
    Set rst = db.OpenRecordSet("tblCustomers", dbOpenDynaset)
    rst.Lockedits = True  'Invoke Pessimistic Locking
    strCriteria = "[CustomerID] = '" & strCustID & "'"
    rst.FindFirst strCriteria
    rst.Edit   'Lock Occurs Here
    rst!City = "Thousand Oaks"
    rst.Update 'Lock Released Here
End Sub

This code, and the rest of the code in this chapter, can be found in CHAP18EX.MDB on the sample code CD.

In this scenario, although the lock occurs for a very short period of time, it is actually being issued at the edit. It is then released upon update.

The advantage of this method of locking is that you can ensure that no changes are made to the data between the time that the edit method is issued and the time that the update method is issued. Furthermore, when the edit method succeeds, you are ensured write access to the record. The disadvantage is that the time between the edit and the update might force the lock to persist for a significant period of time, locking other users out of not only that record but the entire page of records within which the edited record is contained. This phenomenon is exacerbated when transaction processing is invoked. Transaction processing is covered in Chapter 21, "Transaction Processing." Basically, transaction processing ensures that when you make multiple changes to data, all changes complete successfully or no changes occur. For now, let's look at how pessimistic record locking affects transaction processing. Here's an example:

Sub PessimisticTrans(strOldCity As String, strNewCity As String)
    Dim wrk As Workspace
    Dim db As Database
    Dim rst As Recordset
    Dim strCriteria As String
    Set wrk = DBEngine(0)
    Set db = CurrentDb()
    Set rst = db.OpenRecordSet("tblCustomers", dbOpenDynaset)
    rst.Lockedits = True  'Pessimistic Locking
    strCriteria = "[City] = '" & strOldCity & "'"
    rst.FindFirst strCriteria
    wrk.BeginTrans
    Do Until rst.NoMatch
        rst.Edit   'Lock Occurs Here
        rst!City = strNewCity
        rst.Update
        rst.FindNext strCriteria
    Loop
    wrk.CommitTrans  'Lock released here
End Sub

Here you can see that the lock is in place from the very first edit that occurs until the CommitTrans is issued. This means that no one can update any pages of data involving the edited records until the CommitTrans is issued. This can be prohibitive during a long process.

Optimistic Locking

Optimistic locking delays the time at which the record is locked. The lock is issued upon update rather than edit. The code looks like this:

Sub OptimisticLock(strCustID As String)
    Dim db As Database
    Dim rst As Recordset
    Dim strCriteria As String
    Set db = CurrentDb()
    Set rst = db.OpenRecordSet("tblCustomers", dbOpenDynaset)
    rst.Lockedits = False 'Optimistic Locking
    strCriteria = "[CustomerID] = '" & strCustID & "'"
    rst.FindFirst strCriteria
    rst.Edit
    rst!City = "Thousand Oaks"
    rst.Update 'Lock Occurs and is Released Here
End Sub

As you can see, the lock does not happen until the Update method is issued. The advantage of this method is that the page is locked very briefly. The disadvantage of this method occurs when two users grab the record for editing at the same time. When one user attempts to update, no error occurs. When the other user attempts to update, she receives an error indicating that the data has changed since her edit was first issued. The handling of this error message is covered later in this chapter.

Optimistic locking with transaction handling is not much different from pessimistic locking. As the code reaches the Update method for each record, that record is locked. The code appears as follows:

Sub OptimisticTrans(strOldCity As String, strNewCity As String)
    Dim wrk As Workspace
    Dim db As Database
    Dim rst As Recordset
    Dim strCriteria As String
    Set db = CurrentDb()
    Set rst = db.OpenRecordSet("tblCustomers", dbOpenDynaset)
    rst.Lockedits = False  'Optimistic Locking
    strCriteria = "[City] = '" & strOldCity & "'"
    rst.FindFirst strCriteria
    wrk.BeginTrans
    Do Until rst.NoMatch
        rst.Edit
        rst!City = strNewCity
        rst.Update   'Lock Occurs
        rst.FindNext strCriteria
    Loop
    wrk.CommitTrans  'Locks are Released Here
End Sub

Effectively Handling Locking Conflicts

If a user has a page locked and another user tries to view data on that page, no conflict occurs. On the other hand, if other users attempt to edit data on that same page, they experience an error.

You won't always want Access's own error handling to take over when a locking conflict occurs. For example, rather than having Access display its generic error message indicating that a record is locked, you might want to display your own message and then attempt to lock the record a couple of additional times. To do something like this, it is necessary that you learn to interpret each locking error that is generated by VBA, so that you can make a decision about how to respond.

Locking conflicts occur in the following situations:

These errors can occur whether you are editing bound data via a form or accessing the records via VBA code.

Errors with Pessimistic Locking

To begin the discussion of locking conflicts, let's take a look at the types of errors that occur when pessimistic locking is in place. With pessimistic locking, you generally need to code for the following errors:

Coding Around Pessimistic Locking Conflicts

It is fairly simple to write code to handle Pessimistic locking conflicts. Here is an example of what your code should look like:

Sub PessimisticRS(strCustID As String)
    On Error GoTo PessimisticRS_Err
    Dim db As Database
    Dim rst As Recordset
    Dim strCriteria As String
    Dim intChoice As Integer
    Set db = CurrentDb()
    Set rst = db.OpenRecordSet("tblCustomers", dbOpenDynaset)
    rst.LockEdits = True  'Invoke Pessimistic Locking
    strCriteria = "[CustomerID] = '" & strCustID & "'"
    rst.FindFirst strCriteria
    rst.Edit   'Lock Occurs Here
    rst!City = "Thousand Oaks"
    rst.Update 'Lock Released Here
    Exit Sub
PessimisticRS_Err:
    Select Case Err.Number
        Case 3197
            rst.Move 0
            Resume
        Case 3260
            intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
            Select Case intChoice
               Case vbRetry
                  Resume
               Case Else
                  MsgBox "Couldn't Lock"
            End Select
        Case 3167
            MsgBox "Record Has Been Deleted"
        Case Else
            MsgBox Err.Number & ": " & Err.Decription
        End Select
End Sub

The error-handling code for this routine handles all of the errors that can occur with Pessimistic locking. If a Data Has Changed error occurs, the data is refreshed by the rs.Move 0, and the code resumes on the line causing the error, forcing the Edit to be reissued. If a 3260 error occurs, the user is asked whether she wants to try again. If she responds affirmatively, the Edit is reissued; otherwise, the user is informed that the lock failed. If the record being edited has been deleted, an error 3167 occurs. The user is informed that the record has been deleted. Here's what the situation looks like when transaction processing is involved:

Sub PessimisticRSTrans()
    On Error GoTo PessimisticRSTrans_Err
    Dim wrk As Workspace
    Dim db As Database
    Dim rst As Recordset
    Dim intCounter As Integer
    Dim intTry As Integer
    Dim intChoice As Integer
    Set wrk = DBEngine(0)
    Set db = CurrentDb
    Set rst = db.OpenRecordSet("tblCustomers", dbOpenDynaset)
    rst.LockEdits = True
    wrk.BeginTrans
    Do While Not rst.EOF
        rst.Edit
        rst![CompanyName] = rst![CompanyName] & "1"
        rst.Update
        rst.MoveNext
    Loop
    wrk.CommitTrans
    Exit Sub
PessimisticRSTrans_Err:
    Select Case Err.Number
        Case 3197
            rst.Move 0
            Resume
        Case 3260
            intCounter = intCounter + 1
            If intCounter > 2 Then
                intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
                Select Case intChoice
                    Case vbRetry
                        intCounter = 1
                    Case vbCancel
                        Resume CantLock
                End Select
            End If
            DoEvents
            For intTry = 1 To 100: Next intTry
            Resume
        Case Else
            MsgBox "Error: " & Err.Number & ": " & Err.Description
        End Select
CantLock:
    wrk.Rollback
    Exit Sub
End Sub

This code attempts to lock the record. If it is unsuccessful (that is, an error 3260 is generated), it tries three times and then prompts the user for a response. If the user selects Retry, the process repeats. Otherwise, a rollback occurs and the subroutine is exited. If a Data Has Been Changed error occurs, the subroutine refreshes the data and tries again. If any other error occurs, the Rollback is issued and none of the updates are accepted.

Errors with Optimistic Locking or New Records

Now that you have seen what happens when a conflict occurs with Pessimistic locking, let's see what happens when Optimistic locking is in place or when users are adding new records. The three most common error codes that are generated by locking conflicts when Optimistic locking is in place are as follows:

Coding Around Optimistic Locking Conflicts

Remember that with optimistic locking, VBA attempts to lock the page when the Update method is issued. There is a strong chance that a 3197 (data has changed) error could occur. This needs to be handled within your code. Let's modify the preceding subroutine for optimistic locking.

Sub OptimisticRS(strCustID)
    On Error GoTo OptimisticRS_Err
    Dim db As Database
    Dim rst As Recordset
    Dim strCriteria As String
    Dim intChoice As Integer
    Set db = CurrentDb()
    Set rst = db.OpenRecordSet("tblCustomers", dbOpenDynaset)
    rst.Lockedits = False 'Optimistic Locking
    strCriteria = "[CustomerID] = '" & strCustID & "'"
    rst.FindFirst strCriteria
    rst.Edit
    rst!City = "Thousand Oaks"
    rst.Update 'Lock Occurs and is Released Here
    Exit Sub
OptimisticRS_Err:
    Select Case Err.Number
        Case 3197
            If rst.EditMode = dbEditInProgress Then
                intChoice = MsgBox("Overwrite Other User's Changes?", _
                vbYesNoCancel + vbQuestion)
                Select Case intChoice
                    Case vbCancel, vbNo
                        MsgBox "Update Cancelled"
                    Case vbYes
                        rst.Update
                        Resume
                End Select
            End If
        Case 3186, 3260  'Locked or Can't Be Saved
            intChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
                Select Case intChoice
                    Case vbRetry
                        Resume
                    Case vbCancel
                        MsgBox "Udate Cancelled"
                End Select
        Case Else
            MsgBox "Error: " & Err.Number & ": " & Err.Description
        End Select
End Sub

As with the pessimistic error handling, this routine traps for all potential errors that can occur with optimistic locking. In the case of a Data Has Changed conflict, the user is warned of the problem and asked whether she wants to overwrite the other user's changes or cancel her own changes. In the case of a locking conflict, the user is asked whether she wants to try again. Here's what it looks like with transaction processing involved:

Sub OptimisticRSTrans()
    On Error GoTo OptimisticRSTrans_Err
    Dim db As Database
    Dim rs As Recordset
    Dim iCounter As Integer
    Dim iTry As Integer
    Dim iChoice As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordSet("tblCustBackup", dbOpenDynaset)
    rs.Lockedits = False
    BeginTrans
    Do While Not rs.EOF
        rs.Edit
        rs![CompanyName] = rs![CompanyName] & "1"
        rs.Update
        rs.MoveNext
    Loop
    CommitTrans
    Exit Sub
OptimisticRSTrans_Err:
    Select Case Err.Number
        Case 3197
            If rs.EditMode = dbEditInProgress Then
                iChoice = MsgBox("Overwrite Other User's Changes?", _
                vbYesNoCancel + vbQuestion)
                Select Case iChoice
                    Case vbCancel, vbNo
                        Resume RollItBack
                    Case vbYes
                        'rs.Update
                        Resume
                End Select
            End If
        Case 3186, 3260  'Locked or Can't Be Saved
            iCounter = iCounter + 1
            If iCounter > 2 Then
                iChoice = MsgBox(Err.Description, vbRetryCancel + vbCritical)
                Select Case iChoice
                    Case vbRetry
                        iCounter = 1
                    Case vbCancel
                        Resume RollItBack
                End Select
            End If
            DoEvents
            For iTry = 1 To 100: Next iTry
            Resume
        Case Else
            MsgBox "Error: " & Err.Number & ": " & Err.Description
        End Select
RollItBack:
    Rollback
    Exit Sub
End Sub

If a Data Has Changed conflict occurs and the user opts to not overwrite the other user's changes, the entire processing loop is canceled (a rollback occurs). If a locking error occurs, the lock is retried several times. If it is still unsuccessful, the entire transaction is rolled back.

Testing to See Who Has a Record Locked

Regardless of what type of error occurs, it is often useful to find out who has locked a particular record. This can be easily accomplished using VBA code. It is simply a matter of parsing the Description property of the Err object.

Sub WhoLockedIt()
   On Error GoTo WhoLockedIt_Err
   Dim db As Database
   Dim rst As Recordset
   Set db = CurrentDb
   Set rst = db.OpenRecordset("tblCustomers", dbOpenDynaset)
   rst.Edit
   rst!CompanyName = "Hello"
   rst.Update
   Exit Sub
WhoLockedIt_Err:
   Dim strName As String
   Dim strMachine As String
   Dim intMachineStart As Integer
   intMachineStart = InStr(43, Err.Description, " on machine ") + 13
   If Err = 3260 Then
      strName = Mid(Err.Description, 44, _
               InStr(44, Err.Description, "'") - 44)
      strMachine = Mid(Err.Description, intMachineStart, _
               Len(Err.Description) - intMachineStart - 1)
   End If
   MsgBox strName & " on " & strMachine & " is the culprit!"
End Sub

The error description when a locking conflict occurs is pictured in Figure 18.11. The preceding routine simply parses the standard error description, pulling out the user name and machine name. The custom error message appears in Figure 18.12.


Figure 18.11. The Standard Locking error message with machine and description.


Figure 18.12. The Custom Locking error message with machine and description.

Testing a Record for Locking Status

Often, you want to determine the locking status of a record before you attempt an operation with it. By setting the LockEdits property of the recordset to True and attempting to modify the record, you can determine whether the current row is locked. The code looks like this:

Sub TestLocking()
   Dim db As Database
   Dim rst As Recordset
   Dim fLocked As Boolean
   Set db = CurrentDb
   Set rst = db.OpenRecordset("tblCustomers", dbOpenDynaset)
   fLocked = IsItLocked(rst)
   MsgBox fLocked
End Sub
Function IsItLocked(rstAny As Recordset) As Boolean
   On Error GoTo IsItLocked_Err
   IsItLocked = False
   With rstAny
      .LockEdits = True
      .Edit
      .MoveNext
      .MovePrevious
   End With
   Exit Function
IsItLocked_Err:
   If Err = 3260 Then
      IsItLocked = True
      Exit Function
   End If
End Function

The TestLocking routine sends its recordset to the IsItLocked function. The IsItLocked function receives the recordset as a parameter and sets its LockEdits property to True. It then issues an Edit method on the recordset. If an error occurs, the record is locked. The error handler sets the return value for the function to True.

Using Code to Refresh or Requery

Throughout the chapter, references have been made to the need to Requery a recordset. In this section, you will see how to accomplish the Requery process using code.

The Requery method ensures that the user gets to see any changes to existing records, as well as any records that have been added. It also ensures that deleted records are removed from the recordset. It is easiest to understand the Requery process by looking at the data underlying a form.

Private Sub cmdRequery_Click()
   If Me.RecordsetClone.Restartable Then
      Me.RecordsetClone.Requery
   Else
      MsgBox "Requery Method Not Supported on this Recordset"
   End If
End Sub

This code first tests the Restartable property of the recordset underlying the form. If the Restartable property is True, the recordset supports the Requery method. The Requery method is performed on the form's recordset. Of course, the Restartable property and Requery method work on any recordset, not just the recordset underlying a form. The only reason that a recordset might not be restartable is because some back-end queries cannot be restarted.

Prior to the running of this code, new records do not appear in the recordset and deleted records appear with #Deleted (see Figure 18.12). After the Requery method is issued, all new records appear, and deleted records are removed.


Figure 18.13. A recordset that has not yet been requeried.

Understanding the LDB File

Every database that is opened for shared use has a corresponding .LDB file. This is a locking file that is created to store computer and security names and to place byte range locks on the recordset. The .LDB file always has the same name and location as the databases whose locks it is tracking, and it is automatically deleted when the last user exits the database file. There are two times when the .LDB file is not deleted:

The Jet database engine writes an entry to the .LDB file for every user who opens the database. The size of the entry is 64 bytes. The first 32 bytes contains the user's computer name, and the last 32 bytes contains the user's security name. Because the maximum number of users for an Access database is 255, the .LDB file can get only as large as 16K. The .LDB file information is used to prevent users from writing data to pages that other users have locked and to determine who has the pages locked.

When a user exits an Access database, the user's entry in the .LDB file is not removed. Instead, the entry is overwritten by the next person accessing the database. For this reason, the .LDB file does not provide an accurate picture of who is currently accessing the database.

Creating Custom Counters

Access provides an AutoNumber field type. The AutoNumber field can be set to automatically generate sequential or random values. Although the AutoNumber field type is sufficient for most situations, you might want to home-grow your own AutoNumber fields for any of the following reasons:

To generate your own automatically numbered sequential value, you should probably build a system table. This table contains the next available value for your custom autonumber field. It is important that you lock this table while a user is grabbing the next available sequential value. Otherwise, it is possible that two users will be assigned the same value.

Unbound Forms

One solution to locking conflicts is to use unbound forms. Using unbound forms, you can greatly limit the amount of time that a record is locked, and you can fully control when Access attempts to secure the lock. Unbound forms require significantly more coding than bound forms. You should make sure that the benefits you receive from using unbound forms outweigh the coding and maintenance involved. With improvements to both forms and the Jet engine in Access 95, the reasons to use unbound forms with Access data are less compelling. Unbound forms are covered in more detail in Chapter 20, "Client/Server Techniques."

Using Replication to Improve Performance

Replication, covered in Chapter 23, can be used to improve performance in a multiuser application. Using replication, you can place multiple copies of the database containing the tables out on the network, each on a different file server. Different users can be set up to access data from the different file servers, thereby better distributing network traffic. Using the Replication Manager, the databases can be synchronized at regular intervals. Although this is not a viable solution when the data that users are viewing needs to be fully current, there are many situations in which this type of solution might be adequate. It is often the only solution when limited resources do not allow the migration of an application's data to a Client/Server database.


The Replication Manager comes only in the ADT.

Practical Examples: Making an Application Multiuser Ready

The Time and Billing application is built mostly around forms. Because it is unlikely that two users will update the data in the same record at the same time, you can opt for Optimistic locking. This reduces the chance of a page of records inadvertently being locked for a long period of time. The following example illustrates how you can use the error event of the form to override the default locking error messages.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
   On Error GoTo Form_Error_Err:
   Dim intAnswer As Integer
   If DataErr = 7787 Then  'Data Has Changed Error
      intAnswer = MsgBox("Another User Has Modified This Record " & vbCrLf & _
         "Since You Began Editing It. " & vbCrLf & vbCrLf & _
         "Do You Want To Overwrite Their Changes? " & vbCrLf & _
         "Select YES to Overwrite, NO to Cancel Your Changes", _
         vbYesNo, "Locking Conflict")
   End If
   If intAnswer = vbYes Then
      Dim db As DATABASE
      Dim rst As Recordset
      Dim strSQL As String
      Dim fld As Field
      strSQL = "Select * from tblClients Where ClientID = " & Me!ClientID
      Set db = CurrentDb
      Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
      For Each fld In rst.Fields
         rst.Edit
         If Nz(fld) <> Nz(Me(fld.Name)) Then
            fld.Value = Me(fld.Name).Value
         End If
         rst.UPDATE
      Next fld
   End If
   Response = acDataErrContinue
   Exit Sub
Form_Error_Err:
      MsgBox "Error # " & Err.Number & ": " & Err.Description
      Exit Sub
End Sub

This code is placed within the Error event of the frmClients form in the CHAP18.MDB database. It begins by checking to see whether the DataErr is equal to 7787. This is the error code for DataErr when a Data Has Changed error has occurred within a form. If a 7787 has occurred, the user is presented with a message box, notifying her of the conflict and asking whether she wants to overwrite the other user's changes or cancel her own changes (see Figure 18.14). This routine could be enhanced to allow the user to view the other user's changes. If the user responds that she wants to overwrite the other user's changes, a new recordset is created based on the ClientID of the current record. This recordset contains all of the values that the other user has entered. The code loops through each field in the recordset. It issues an edit method on the recordset. Next, it tests to see whether the value of the current field matches the value of the control on the form with the same name. If there is a match, the value of the field is replaced with the value in the form. Regardless of whether the user responds Yes or No, the Response is set equal to acDataErrContinue so that the normal error message is suppressed.


Figure 18.14. The custom message box that is displayed when trapping for a locking conflict.

Summary

VBA makes several alternative locking strategies available to the developer. These range from locking the entire database to locking one page of records at a time. In addition, VBA enables you to determine how long data will be locked. This feature is manipulated through techniques of optimistic and pessimistic locking. The developer must select which combination of strategies should be implemented in each particular application. The decision about which methodology to use will be influenced by many factors, including the volume of traffic on the network and the importance of ensuring that collisions never occur.

Previous Page TOC Index Next Page Home