Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 8.7 Accelerate Multiuser Applications

8.7.1 Problem

You have a single-user application that you just converted to run on a network to be shared by multiple users. Your once-responsive application is now sluggish. How can you improve the performance of multiuser applications?

8.7.2 Solution

Moving a single-user application to a shared environment can make that application slower for at least three reasons. First, to read or write data from the database, the data must now travel across relatively slow network wires. This is almost always slower than reading and writing data directly to a local hard disk drive. Second, every time a record is written to disk, Access must spend time obtaining, releasing, and managing locks to make sure that two users do not write to a page of records at the same time. Third, if multiple users are trying to access the same records in the database, they must wait their turns before gaining access to the records. Because of these factors, you need to make an extra effort to optimize multiuser applications to bring their speed to an acceptable level. This solution discusses one way to improve performance by limiting the number of records in your form's recordsets.

This solution employs two files, 08-07FE.MDB and 08-07BE.MDB. You'll first need to link the data tables from 08-07BE.MDB (the "backend" or data database) to 08-07FE.MDB (the "frontend" or application database). Linking a data table allows you to use a table from one Access database within another Access database. Start Access and load 08-07FE.MDB. Choose File Get External Data Link Tables and select 08-07BE.MDB as the Access link database. At the Link Tables dialog, select tblPeople and click OK. (To appreciate the extra demands made on a multiuser application, you may wish to move the 08-07BE.MDB database to a file server on your local area network first.)

Splitting Multiuser Databases

This solution makes use of a common multiuser technique: splitting the application and data into separate databases. Multiuser application performance can be improved considerably if you place the data (or backend database) on the file server and a copy of the application (or frontend database) on each user's desktop. Access includes the Database Splitter Wizard, which makes it easy to split an existing database into data and application databases. Select Tools Database Utilities Database Splitter to run the wizard.

Once you've fixed up the link to tblPeople in the backend database, open the frmPeopleFindFirst form in form view and note how long it takes to load the form. Enter the value 60000 into the text box in the header of the form. Press the Goto Record button to move to the record with an ID of 60000. The time this operation takes is displayed to the right of the command button (see Figure 8-16).

Figure 8-16. The frmPeopleFindFirst form

Now close the form and open the frmPeopleRSChange form in form view. This form is similar to frmPeopleFindFirst, except that it initially loads with only one record in its recordset. Because of this, load time should be faster than for frmPeopleFindFirst. This form also differs in how it searches for records. Instead of using the potentially slow FindFirst method to navigate to a different record, it changes the record source of the form on the fly. Enter the value 60000 into the text box in the header of frmPeopleRSChange and press the Goto Record button. The time this operation takes should be faster than for frmPeopleFindFirst (see Figure 8-17).

Figure 8-17. The more multiuser-friendly frmPeopleRSChange form

Although the performance difference between these two forms may be noticeable with 60,000 records in the sample database, it's not that great. With more records or across a busy network, however, the difference will be much more significant. Still, even without a noticeable performance improvement, this technique significantly reduces the load you are placing on the network.

Follow these steps to create a form that uses the record source changing technique of frmPeopleRSChange:

Create a new form or edit an existing form in design view. Change the RecordSource property of the form so that it initially loads no records. The most efficient way to do this is to use a Select statement that restricts the record source by its primary key field to a nonexistent record. For example, we used the following record source for frmPeopleRSChange:
```
SELECT * FROM tblPeople WHERE ID = 0
```
This will cause Access to place you on the new record that's normally at the end of a form's recordset. If you prefer, you can use a Select statement that returns some small subset of the records instead.
Add an unbound text box named txtGoto to the form's header. Add a command button control named cmdGoto to the right of the text box.
Create a new event procedure for the Change event of the txtGoto text box. (If you're unsure of how to do this, see How Do I Create an Event Procedure? in the Preface of this book.) Add the following code to the event procedure:
```
Private Sub txtGoto_Change( )
        
    ' Enable cmdGoto only when a character
    ' has been typed into txtGoto
        
    Me.cmdGoto.Enabled = (Not IsNull(Me.txtGoto.Text))
        
End Sub
```

Create a new event procedure for the Click event of cmdGoto and add code similar to the following (or copy the cmdGoto_Click event procedure from frmPeopleRSChange; however, that event procedure has additional code that times the operation, which is eliminated here):

Private Sub cmdGoto_Click( )

    ' Go to new record by changing the
    ' form's RecordSource property
    
    Dim ctlGoto As TextBox
    Dim ctlTime As TextBox
    Dim varCriteria As Variant
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim dblTime As Double
    
    On Error GoTo HandleErr
    
    Const acbcQuote = """"
    
    ' Start the timer.
    lngStart = acb_apiGetTickCount( )
    
    Set ctlGoto = Me.txtGoto
    Set ctlTime = Me.txtTime
    
    ' Create criteria based on the type of data
    ' entered into txtGoto.
    If IsNumeric(ctlGoto.Value) Then
        varCriteria = "ID = " & CLng(ctlGoto.Value)
    Else
        ' A string, so search LastName.
        varCriteria = "LastName Like " & acbcQuote & _
         ctlGoto.Value & "*" & acbcQuote
    End If
    
    ' Change the form's recordset based on criteria.
    Me.RecordSource = "SELECT * FROM tblPeople WHERE " _
     & varCriteria
    
    lngEnd = acb_apiGetTickCount( )

    ' Now check the form's recordset to see if
    ' any records were found.
    
    With Me.Recordset
        If .EOF And .BOF Then
            MsgBox "No matching record found.", _
             vbOKOnly + vbCritical, "Goto Procedure"
        End If
    End With
    
    ' Post the time to txtTime.
    dblTime = (lngEnd - lngStart) / 1000
    ctlTime = "Operation took " & Format(dblTime, "##0.00") _
     & " seconds"
    
ExitHere:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
    Case Else
        MsgBox "Error#" & Err.Number & ": " & Err.Description, _
         , "Goto Procedure"
        Resume ExitHere
    End Select
End Sub

See Recipe 8.7.3 for information on how to customize this code for your particular form.

Save the form and switch to form view to test it.

8.7.3 Discussion

In a multiuser environment, it's always important to limit the amount of data sent across the network to your desktop. By default, however, Access binds forms to all records in the table or query to which your form is bound. This is fine for smaller recordsets of perhaps less than 20,000 records (the exact cutoff figure will vary based on the speed of your PCs, the speed of your network cards and file server, and the average network load), but it can slow things considerably for moderately large recordsets. This solution improves the performance of the form and reduces network traffic by carefully limiting the records in the form's recordset.

By using a SQL statement that initially returns no records as the form's record source, you can quickly open the form in append mode. When the user enters a value in the txtGoto text box and presses the Goto Record button, code attached to the button's Click event changes the form's RecordSource to the correct record.

The event procedure behind the cmdGoto command button begins by setting up an error handler, declaring a few variables, and setting ctlGoto to point to the txtGoto text box control:

On Error GoTo cmdGotoClickErr

Dim ctlGoto As TextBox
Dim varCriteria As Variant

Const acbQuote = """"

Set ctlGoto = Me.txtGoto

Next, the criteria of the SQL Select statement is constructed using this code:

' Create criteria based on the type of data
' entered into txtGoto.
If IsNumeric(ctlGoto.Value) Then
    varCriteria = "ID = " & CLng(ctlGoto.Value)
Else
    ' A string, so search LastName
    varCriteria = "LastName Like " & acbQuote & _
     ctlGoto.Value & "*" & acbQuote
End If

In the case of the people form, we decided to be flexible and allow users to search on either last name or ID. You'll want to make sure the fields you allow the user to search are indexed. The code determines which field the user wishes to search by using the IsNumeric function to test if the entered value is a number. If so, the code constructs criteria using the ID field of tblPeople. If the entered value is non-numeric, then the code assumes the user wishes to search on LastName. Again, we add a bit of flexibility by allowing the user to enter partial matches—the criteria string is constructed using the Like operator. Because this is a Text field, we must surround the value with quotes, so we use the acbcQuote constant that we defined earlier in the procedure. Finally, we have added "*" (an asterisk) before the closing quote to perform a pattern match search.

If you wish, you can simplify this code on your own form to use a single field. Either way, you'll need to change the references to ID and LastName to match the names of the fields (not the control names) in your form's record source. If you decide to allow a search on a date/time field, make sure you surround the date/time value with # (pound signs) instead of quotes.

With the criteria built, the SQL statement is easily created:

' Change the form's recordset based on criteria.
Me.RecordSource = "SELECT * FROM tblPeople WHERE " & varCriteria

Of course, you'll need to replace tblPeople with the name of the table or query on which your form is based.

The remaining code determines if any records were found:

' Now check the form's recordset to see if
' any records were found.

With Me.Recordset
    If .EOF And .BOF Then
        MsgBox "No matching record found.", _
         vbOKOnly + vbCritical, "Goto Procedure"
    End If
End With

This portion of code is not absolutely required, because Access will pull up the "new" record if no matching records are found. However, you might prefer to notify the user when no records were found. You can do this by using the form's Recordset property to return a recordset object that you can inspect. If the recordset is empty, Access sets both the end of file (EOF) and beginning of file (BOF) flags to True, so you can use this fact to test for the absence of records in the form's recordset.

A simple error handler is included in this procedure. It's important to include error-handling code in all multiuser procedures to handle the cases where records are locked. See Chapter 10 for more information on developing multiuser applications.

The one negative side to using this technique is that users may find it restrictive if they are used to navigating freely among records using the navigation controls at the bottom of the form. The sample form allows users to grab a subset of records from tblPeople by entering a partial match on LastName. If you also need to return groups of records when using numeric primary key field searches, you can use two text boxes to allow users to search for a range of primary key values, perhaps including code that limits the range to some arbitrary number.

The techniques presented in this solution apply equally to client/server applications.

8.7.4 See Also

Additional optimization strategies for client/server applications are discussed in the Solution in Recipe 8.8 and in Chapter 14.

[ Team LiB ]