Multiuser Considerations


Various coding approaches can affect how well your application will handle multiple users. For example, the type of record lock that is used on records retrieved from the database will affect what happens when multiple users try to access the same record. When the application does not handle the scenario correctly, update conflicts can occur because one user overwrites the changes made by another user. If your application uses the proper type of record lock and handles update conflicts, it will be more capable of supporting multiple users. Another way to help your application better handle multiple users is to separate the data tables from the other objects by using two separate Access database files. Both of these techniques are discussed in the sections that follow.

Record Locking and Update Conflicts

Prior to reading this book, you likely worked with bound forms in your Access applications. Bound forms are connected directly to a table and keep an open connection to the database so all changes are made at that moment. Then, in Chapter 5, you learned how to populate an ADO recordset with data from a database and how to display records from the recordset on both bound and unbound forms.

Let’s now look at how record locking is handled on bound and unbound forms and the ways you can modify record-locking settings to better handle multiple users.

Record Locking on Bound Forms

The type of record locking that is used for bound forms is determined by the Options settings. To modify the record locking options for bound forms, select the Office button, and at the button on the pop-up menu click on Access Options, and the select Advanced on the left menu. A dialog box like the one shown in Figure 12-1 is displayed. You may have to scroll down to see the options shown in Figure 12-1.

image from book
Figure 12-1

The three options for default record locking are No Locks, All Records, and Edited Record. If the No Locks option is selected, no records will be locked and all users will be able to make edits. This can result in one user stepping on the toes of another user by overwriting his changes without realizing it. If the All Records option is selected, all records in the underlying table of the form you’re editing will be locked. This option is generally not good for multiuser applications except for the specific situations where you want to keep everyone else out while one user has the table open. A third option is to select the Edited Record option. This option locks only the specific record that the user is editing and is the preferred setting for most multiuser instances. However, it is not the default setting in Access. If the Open Databases Using Record-Level Locking option is enabled, record locking will be the default for the current database. If the option is unchecked, then page-level locking will be the default for the current database.

Tip 

Your code will always override Access’s default settings.

Record Locking on Unbound Forms

With unbound forms, the type of locking used depends on locking the source where the data comes from. For example, if your form is based on an ADO recordset, the lock type of the recordset is what determines locking. As you may recall from Chapter 5, the LockType property of the Recordset object can be one of four settings, shown in the following table.

Open table as spreadsheet

Value

Explanation

adLockReadOnly

The recordset is read-only, and no changes are allowed.

adLockPessimistic

The record in the recordset will be locked as soon as editing begins.

adLockOptimistic

The record in the recordset will be locked when the Update method is issued.

adLockBatchOptimistic

The records will not be locked until a batch update of all records is performed.

An example of the syntax of this property is shown here:

  rsRecordset.LockType = adLockReadOnly 

Read-only locks should be used whenever the users do not need to update the data because they are faster and do not lock records unnecessarily. For multiuser applications, pessimistic and optimistic options are the best when users must update the data. With pessimistic locking, the record in the record-set is locked as soon as editing begins. This prevents any other user from changing that record until the first user has finished editing it.

With optimistic locking, the record is not locked until the very moment that the update command is processed. This option allows all users to interact with the same record even if someone else has it open. However, optimistic locking also presents the potential problem of having update conflicts. By default, you should use pessimistic locking when editing records in code, unless otherwise necessary.

Update Conflicts

An update conflict can occur when a first user is updating a record while a second user still has the old data on the screen. When the second user saves the changes to the database, some of the values being updated are old values that overwrite the changes made by the first user.

If you want to use optimistic locking, you should write code to handle update conflicts. You do so to avoid the possibility of one user overwriting another user’s changes. One way to handle update conflicts in VBA code is to add a timestamp or version number value to each table so that you can tell the last time a record was updated. If you use this approach, you must keep track of the original value of the version number or timestamp when the record is first open, and then before updating the database you must check to see if the version number or timestamp has changed. Another way to handle update conflicts is to check for changes on a field-by-field basis and then notify the user of exactly which fields changed so that he can decide whether to overwrite the record.

The topic of update conflicts could take an entire chapter, but you should at least be aware of what it means and some ways to handle it. You can obtain more information about handling update conflicts by searching the articles on msdn.microsoft.com. Let’s turn now to another consideration for improving multiuser applications.

Multiuser Architecture Considerations

One way to improve the capability of multiple users to work with an Access application is to separate the data tables from the user-interface aspects. If you put a single Access database that contains the entire application on a file server and allow multiple users to access it simultaneously, you are asking for trouble. A better solution is to separate the user interface from the data tables so that each user has his own copy of the user interface. The data tables can be stored in a separate database file on a server and accessed from each copy of the user interface. This approach is illustrated in Figure 12-2.

image from book
Figure 12-2

Notice that there is one central database for the data tables, and all users have a copy of the user-interface database with objects/code on their computers. The user-interface database is then linked to the database containing the tables.

This approach also improves the performance of the application as the number of users grows, as I discuss in the performance section later in this chapter.

Ideally, you should design your database using this approach from the very beginning. Simply create the database containing the tables in one database, create the database containing the user-interface objects in another database, and then link to the database containing the tables. To link to the database containing the tables, select the External Data ribbon image from book Access option from the Import ribbon, specify the database containing the tables to link, and select the Link The Data Source option. Follow the prompts to complete the linking. See Chapter 7 for more information on how to link to another database using VBA code.

Another option is to build the application in one database first and then perform the separation manually or by using the Database Splitter. To do so manually, you simply export the data tables to another database, delete the tables from your original database so that only the linked tables remain, and add a link to that external database. Now let’s look at how to use the Database Splitter Wizard.

Try It Out-Using the Database Splitter Wizard to Separate Database Tables from the User Interface

image from book

Using the Database Splitter, you can easily separate an existing Access database into two database files: one containing the tables and the other for the user interface. Let’s walk through an example of how to do this.

  1. Open the existing database you want to split. Open the Northwind.accdb sample database if you have it on your computer. If not, open one of the databases created in the prior chapters. The steps are the same in either case.

    Tip 

    It is recommended that you make a backup copy of the database before proceeding.

  2. Select Database Tools, and the Access Database from the Move Data ribbon, as shown in Figure 12-3.

    image from book
    Figure 12-3

  1. A screen like the one shown in Figure 12-4 is displayed. Select the Split Database button.

    image from book
    Figure 12-4

  2. The Create Back-End Database dialog box is displayed, as shown in Figure 12-5. Specify the name and location of the database where you want the data tables to be stored. If you are splitting the Northwind database, specify a file name of Northwind_be.accdb. Then, click the Split button.

    image from book
    Figure 12-5

  3. After the data is split into the separate database, you receive a message confirming the database was successfully split. Click OK on the dialog box.

  4. You are then returned to the original database, which in this case is Northwind.accdb. As shown in Figure 12-6, you now see that the tables are linked to the newly created Northwind_be.accdb.

    image from book
    Figure 12-6

How It Works

The Database Splitter Wizard allowed you to convert a single Access database into two separate databases: one with the user interface and the other with the data tables. After you ran the Database Splitter, the data tables were moved to a new database and a link was added to the original database to point to the tables in the new database.

After the split is completed, you can copy the new database with the tables to a file server or other location where multiple client computers can access it. You can then copy the user-interface database to each user workstation.

Tip 

Make sure that the links that are created will work from each computer where you plan to load the user-interface database. For example, if the tables are linked as the C: drive, that same location may be a different drive letter on a different client computer. If that is the case, you need to modify the links from the second computer so that it finds the data.

Now that you have a few ideas on how to improve the multiuser capabilities of your application, let’s turn to the various ways you can optimize your application.

image from book




Beginning Access 2007 VBA
Beginning Access 2007 VBA
ISBN: 0470046848
EAN: 2147483647
Year: 2004
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net