Microsoft Access VBA Programming for the Absolute Beginner

Whenever multiple users have access to data, and especially when at least one of those users has the ability to modify or update that data, the possibility of contention arises. One possible scenario involves updates to data and goes something like this: User A accesses a recordset that includes record 1. User B then accesses a more or less identical recordset that includes record 1 as well. User B deletes the record. User A then updates the record that user B deleted. In order to handle conflicts such as this, a record locking scheme is typically used. In this section, we’ll examine the facilities that Access provides to lock records in order to maintain the integrity of data in a multiuser environment.

Actually, we have to examine record locking in two different environments. If data is accessed purely through the Access interface, Access provides its own scheme of record locking, which can be configured through the Access user interface. If data is accessed programmatically using ADO and you make updates to data using the ADO Recordset object, your ADO code is responsible for handling resource contention and conflicts.

Setting the Refresh Interval

The refresh interval determines how long it takes for data changed by one user to be displayed in the session of a second user. The refresh interval is set by the Refresh Interval (Sec) text box on the Advanced tab of the Options dialog box; it represents the number of seconds after which Access will refresh a user’s data. The lower the value (and therefore the shorter the refresh interval), the more network traffic is generated just to update the display of records that a user is already displaying, but the more accurate the data will be. Conversely, the higher the value, the less network traffic, but the greater the probability that a user’s form data will be inaccurate. The refresh interval, an Integer whose value can range from 1 to 32,766 seconds, is expressed as the number of seconds between refreshes.

The refresh interval is an Access-wide setting that applies to all databases opened by Access. Although there isn’t a form property that allows you to override the default refresh interval on a form-by-form basis, you can call the Form object’s Refresh method to update data displayed by the form.

Setting the Update Interval

Two additional settings control how often Access will attempt to update an edited record that is locked. The setting in the Number of Update Retries text box on the Advanced tab of the Options dialog box determines how many attempts Access will make to save an edited record that is locked. The default value is 2, although it can be an Integer ranging from 0 to 10.

The Update Retry Interval (Msec) text box determines how much time (in thousandths of a second) must elapse before each new attempt to save the edited record. The default value of this setting is 250 (an attempt is made to commit the edits to a locked record every 2.5 seconds), though its value is an Integer ranging from 0 to 1000.

In setting these values, you should recognize the trade-off involved here: setting them to higher values virtually insures that in the event of record contention, a record eventually will be saved. On the other hand, the time required to do this can degrade performance on the user’s system to a ridiculous degree. For instance, if both properties are set to their maximum values, the user may have to wait up to 1 minute 40 seconds (10 seconds x 10 tries) to update a single record. On the other hand, setting them too low increases the likelihood that Access will abandon an update if a record is locked.

Record Locking in the Access Interface

A record locking scheme locks records when they are being edited by a particular user so that other users cannot edit them. Record locking reduces the likelihood that collisions will occur when multiple users are updating data simultaneously.

Access allows you to configure the record locking scheme used for forms bound to data in tables or in queries. Record locking for bound forms is controlled by the Default Record Locking group box on the Advanced tab of the Options dialog box (shown earlier in Figure 20-1). The dialog box offers three types of record locking:

• No Locks This is actually a misnomer, since it represents an optimistic locking scheme. Access locks a record only when a user has saved changes to an edited record. (Actually, Access may lock a page of data, which is a buffer containing the current record and possibly the records that surround it that is 4KB in size in Access 2000 and later versions and 2KB in size in earlier versions.) That is, once a user clicks the Save button, selects the File | Save menu option, or navigates away from a modified record to save changes, Access locks a record. The record is not locked while the user is actually editing it. No Locks is the default setting, and it minimizes locking conflicts. Its goal is to insure that updates are atomic—that is, that a record isn’t updated by two users at the same time, causing it to have inconsistent values from both users. This option is best used when the probability that more than one user will edit the same record is very small.

• All Records Access locks the entire table whose records are being edited. Although this is the safest alternative, it is also the most restrictive, since it can easily result in multiple users being locked out of a database completely for long periods of time.

• Edited Record This is a pessimistic locking scheme and is probably the most commonly used form of locking in a multiuser environment. Access locks the record (or the page containing the record, which is a 2KB or 4KB buffer that includes the record) edited by a user as soon as that user begins the editing process, and releases the lock once the edits have either been saved or abandoned.

To make the differences among locking types clear, let’s imagine a scenario in which two users are editing the same record in the tblCustomer table. Table 20-1 shows the effect of the three locking schemes.

Although optimistic locking is clearly different from pessimistic locking and database locking, the latter two lock types are not clearly differentiated from one another in the first scenario. However, if we consider a second scenario, in which User A attempts to edit and save a record at the beginning of the database and User B attempts to edit and save a record at the end of the database (and more than one page away from the record being edited by User A if page-level locking is in effect), you will see the differences, as shown in Table 20-2.

In this scenario, when User A is editing and saving a record, User B is also able to edit and save a record using pessimistic locking, but is locked out of the database if using the No Locks option.

If a user attempts to edit a locked record, Access displays the dialog box shown here:

The user can save the changes despite the fact that another user has modified the record, abandon the changes and copy them to the Windows Clipboard (which allows them to be recalled and reviewed later), or simply discard the changes.

Like many of the other settings on the Advanced tab of the Options dialog box, the Default Record Locking setting applies to the Access application as a whole, and not to the individual database application that is opened.

You can override the default locking method for an individual form by assigning one of these three values—No Locks, All Locks, or Edited Record—to its RecordLocks property. You can also override the default locking method for the data in a report by assigning one of two values (No Locks or All Locks) to its RecordLocks property. The original value of the property is determined by the value assigned in the Default Record Locking group box.

Along with the type of locking, you can also determine whether one or possibly more records are locked. The Open Databases Using Record-Level Locking option on the Advanced tab of the Options dialog box, which appears in Access 2000 and later versions, allows you to lock only the record that a user is editing, or to lock a page that includes the record being edited by a user. The page size also varies depending on the Access version. For Access 98 and earlier versions, the page size is 2KB; for Access 2000 and later versions, it is 4KB. (The number of locked records, of course, depends on the size of an individual record.)

Setting Options Programmatically

Many of the options available through the Options dialog box in the Access user interface can also be set programmatically by calling the SetOption method of the Access Application object. The method’s syntax is

SetOption(OptionName, Setting)

where OptionName is a predefined String argument containing the name of the option to be set, and Setting is the value to which the option should be set. Valid values of Setting and their data types depend on the option being set. The options discussed in this chapter have the values shown in Table 20-3.

Record Locking with ADO

The settings that we’ve examined so far have applied to bound forms based on tables and queries and are applied automatically by Access. However, if your forms use ADO recordsets to display editable data, you must implement record locking programmatically. In the section “Assigning Recordsets Dynamically” in Chapter 12, you have already briefly seen how this is done by setting the LockType property of the ADO Recordset object to one of four possible values:

• adLockReadOnly to lock the entire recordset

• adLockPessimistic to lock a record while it is being edited

• adLockOptimistic to lock a record while it is being saved

• adLockBatchOptimistic to lock out users while records are updated in batch mode

The type of lock can be specified as a parameter to the Open method of the Recordset object, or it can be assigned to the Recordset object’s LockType property before the recordset is opened. For example, the following code sets properties separately before calling the Open method:

Private Sub Form_Open(Cancel As Integer) Dim con As ADODB.Connection Dim recSet As ADODB.Recordset Dim strFrmNm As String Set recSet = New ADODB.Recordset recSet.CursorType = adOpenKeyset recSet.LockType = adLockOptimistic Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\BegVBA\thecornerbookstore.mdb;" recSet.Open "SELECT * FROM tblCustomer", con Set Me.Recordset = recSet recSet.Close con.Close Set recSet = Nothing Set con = Nothing End Sub

The following code is identical, except that it sets the locking type in the call to the Open method:

Private Sub Form_Open(Cancel As Integer) Dim con As ADODB.Connection Dim recSet As ADODB.Recordset Dim strFrmNm As String Set recSet = New ADODB.Recordset Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\BegVBA\thecornerbookstore.mdb;" recSet.Open "SELECT * FROM tblCustomer", con, adOpenKeyset, adLockOptimistic Set Me.Recordset = recSet recSet.Close con.Close Set recSet = Nothing Set con = Nothing End Sub

When ADO rather than Access is managing the recordset you’re working with, it’s also important to recognize that the type of recordset your application uses has a vast impact on performance. For a brief discussion of the types of ADO recordsets and their impact on performance, see Chapter 12.