Best Practices When Using Microsoft Office Access 2003 in a Multi-user Environment
Frank C. Rice
Microsoft® Office Access 2003
Summary: Learn techniques you can use to improve the performance and efficiency of working with your multi-user Access applications. (5 printed pages)
General Best Practices
Opening an Access Project
Changes May Be Discarded in MDBs
Excessive Number of Connections to Database May Degenerate Performance
Optimize the Refresh Interval
Setting Up Your Access Database to Automatically Compact and Repair
The following points represent items you should keep in mind when working with Microsoft® Access databases in a multi-user environment:
- Toggling the default open mode between shared and exclusive does not take effect until the next time the database is opened.
- Be sure to locate shared multi-user database files in shared file directory folders.
- Split the database file into a front-end application database, consisting of the Access objects such as queries, forms, and reports, and a back-end data database, consisting of the tables. The back-end database is saved to the shared file directory and the front-end database is saved on each client computer.
- You can set user-level security so that a user can open the database exclusively. This prevents other users from opening the database. As you will see shortly, this is necessary if you plan on making minor changes to Access objects.
If you try to open an Access project (.adp) file or an Access project extension (.ade) file in a multi-user environment, you may receive an error message similar to the following:
"The database DatabaseName will be opened read-only because one of the following occurred: The file is locked for editing by another user, or the file (or the folder in which it is located) is marked as read-only, or you specified that you wanted to open this file read-only."
This error message occurs because an Access project is strictly a client and has no multi-user capability. The workaround for this issue is to deploy a copy of the .adp file or .ade file to each computer.
Minor design changes made to Access-specific objects, such as forms, reports, macros, modules, or command bars, may be discarded without warning. The reason is that multiple users may open the database (.mdb), and Access cannot obtain an exclusive lock on the database. You can overcome this limitation by opening the database exclusively as a single user. To open a database exclusively:
- On the File menu, click Open.
- In the Look in box, click the drive or folder that contains the Access database.
- Click the arrow next to the Open button, and then click Open Exclusive.
Note You can also open a database by including the /Excl or /Ro parameters on the command line. These open the database in exclusive or read-only mode, respectively.
For more information on this limitation and on what qualifies as a minor change as opposed to a major change, see the Microsoft Knowledge Base article — (285828) ACC2002: Silent Design Changes May Be Discarded in a Multiuser Environment.
Each connection to a Jet database represents an independent client to the database, even when these connections come from the same client process. To optimize performance and network I/O and to reduce the multi-user stress on the back-end database, design the client application to use a single connection to the Jet database, and then share this connection over multiple record sets as needed. This has the added benefit of preventing read/write delays in the client application. By default, there is a slight delay between writing a value to the database and being able to read this updated value when writing and reading on two different Jet connections, even if the two connections reside in the same client process. If you use a single connection, you avoid this issue.
One of the features of Jet 4.0 is called connection control. This feature allows you to control the number of connections to a database. This is useful for limiting the number of simultaneous users of a database. It should be noted that connection control cannot be used to disconnect users from a database. With connection control, you control connections using the Jet OLEDB:Control Control property of the ADO Connection object. To disallow any new users of the database, set the property to 1. To allow new users, set the property to 2.
An example of the code to do this is as follows:
Const AllowUsers = "Allow New Users" Const DisallowUsers = "Disallow New Users" Private Sub cmdShutDown_Click() If cmdShutdown.Caption = DisallowUsers Then CurrentProject.Connection. _ Properties("Jet OLEDB:Connection Control") = 1 cmdShutdown.Caption = AllowUsers Else CurrentProject.Connection. _ Properties("Jet OLEDB:Connection Control") = 2 cmdShutdown.Caption = DisallowUsers End If End Sub
As you make changes to the objects in the database, Access refreshes the database based on a preset interval. You can set the default refresh interval for the database by using that Advanced option tab of the Options dialog box, available from the Tools menu. The default for this option is 60 seconds, which may be too long for some applications. Setting the right interval for the application may involve some experimentation. If you set the value too low, you may create excessive network traffic. For smaller networks, setting the interval at lower values should have no adverse effects.
Note Setting the interval to a long value, Access automatically refreshes the current record whenever you attempt to edit it. This may reduce performance and increase network traffic.
As you work with the data in your database, the records can become discontiguous as they are saved. This increases the size of your database. To ensure optimal performance from your database, you should compact and repair your Access files regularly. On the Tools menu, point to Database Utilities, and then click Compact and Repair Database. But don't stop there! You can also automate this task.
Note The compact and repair will only occur when the last person has closed the database. It does not occur when others have the database open.
In a front-end/back-end database scenario, only the front-end database is compacted and repaired. The back-end database is not touched.
Do the following:
- Open the Access database or Access project that you want Access to compact automatically.
Note In the case of an Access project, only the project file is compacted. This does not affect the data that is stored in the Microsoft SQL Server™ database.
- On the Tools menu, click Options.
- Click the General tab.
- Select the Compact on Close check box.
You can use transactions to group record updates in units that can be committed or rolled back as a whole. Transactions also have the additional characteristic of saving updates in a temporary file instead of in tables. This provides benefits in a multi-user environment. For example, a common use of transactions in a multi-user environment is to make sure users do not see an incomplete view of shared data as it is changed. To illustrate:
Suppose your application is executing code that is updating data while another user is running a report on the data. If you update is not wrapped in a transaction, the other user could receive inconsistent data if some of your data was updated and other data was not. If you are using transactions, this cannot happen as all of the records are updated all at once.
Try not to keep your transactions open too long. All record-level and page-level locks that result from the transaction are kept in place until the transaction is committed or rolled back. This can help reduce concurrency in your multi-user application. Problems in concurrency can occur in a multi-user environment when multiple users are making changes to records that can affect other users. Whether record-level or page-level locking is affected depends on the setting for "Open databases using record-level locking" and the type of data being updated. For instance, large Memo or OLE Object fields would lock entire pages when updated regardless of the record-level locking setting.
This article looks at ways you can improve the way you work in a multi-user environment with Access applications. By utilizing some of these techniques, you can reduce the chance for errors and improve the performance of you databases.