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.
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.
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.
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).
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:
Figure 18.2. The Database Splitter dialog.
Figure 18.3. The Create Back-end Database dialog.
Figure 18.4. The database that has been split.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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 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
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.
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:
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.
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:
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.
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.
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.
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.
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.
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.
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."
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 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.
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.