UNDERSTANDING ACCESS MULTIUSER HANDLING
The best way to get going with Access's multiuser features is to look at some of the options available.
Access has certain options you can use to set up for working on a network. To get started, open the Options dialog by choosing Options from the Tools menu. Next, click the Advanced tab (see Figure 22.2).
Figure 22.2. The Advanced page of the Options dialog lets you set multiuser options as well as other options.
The following sections discuss each option on the Advanced page that deals with multiuser issues.
Note
The DDE Operations and OLE/DDE Timeout options are discussed in Chapter 13, “Driving Office Applications with Automation and DDE.”
Default Record Locking
The settings in the Default Record Locking section set the default mode for record locking. Choices are No Locks (the default), All Records, and Edited Record. The purpose of each option is discussed fully later, in the section “Looking at the Built-In Locking Modes.”
Note
Kudos to Microsoft for now providing true record-level locking for forms, datasheets, and VBA code dealing with recordsets. Bulk query operations still require page-level locking, which used to be all there was. Both locking types are discussed later, in the section “Record-Level Versus Page-Level Locking.” When you open the options dialog to the Advanced page, the Open Databases Using Record-Level Locking check box should be selected by default.
When you set Default Record Locking in the Options dialog, all new forms will have this setting as their default for record locks. Figure 22.3 shows the record locks on the Employee form.
Figure 22.3. Set Default Record Locking to reflect what you want the default Record Locks property on forms to be.
Default Open Mode: Shared Versus Exclusive
The Default Open Mode section specifies just that. It's either shared use or exclusive use when opening a database through the Open dialog (see Figure 22.4). Shared is the default.
Figure 22.4. The Open dialog reflects setting the Default Open Mode to Exclusive.
A user sometimes accidentally opens a database exclusively. If the database is on the network, this can cause problems for other users. To keep users from opening a database exclusively, you can set their permissions on the database object's Open Exclusive property to false. Figure 22.5 shows the User and Group Permissions dialog, with the Object Type set to Database. (To access this dialog, choose Security from the Tools menu, and then User and Group Permissions.) For more information on Access security, see Chapter 21, “Securing Your Application.”
Figure 22.5. Notice the Open Exclusive check box available in the User and Group Permissions dialog.
Number of Update Retries
The Number of Update Retries option in the Options dialog sets the number of retries when Access is trying to save a changed record that's locked by another user. After a number of retries is reached, an error message appears. The default setting is 2; the maximum number of retries is 10.
In code, this error can be trapped with error handling. This and other multiuser errors, as well as how to handle them, are covered later in the section “Coding for Multiuser Error Handling.”
ODBC Refresh Interval
The Open Database Connectivity (ODBC) technology connects Access to other file- management systems. The most common use for ODBC is allowing Access to be used as a front end with SQL Server as a back end.
The ODBC Refresh Interval (Sec) setting is how often Access refreshes whatever recordsets are set up between the front end and the ODBC back end. Information in the refreshed recordsets are data changes, insertions, and deletions. The default interval is 1,500 seconds. The maximum amount is 3,600 seconds.
Refresh Interval
The Refresh Interval (Sec) setting is the amount of time between refreshes of recordsets on networks between the front end and back end. This interval is in seconds, and the default is 60. The maximum is 32,766 seconds.
Tip
If an application needs data refreshed up to the second, you can set this interval to be smaller. Remember, however, that when you refresh more often, you also cause more network traffic, which can cause a great amount of degradation for the network overall.
The default is a good setting to start with. Then you can adjust the interval and monitor performance to get the optimal setting. It's different for various network setups.
Update Retry Interval
The Update Retry Interval is just that: the time between successive retries to update a record. The interval is in milliseconds, and the default is 250. The maximum is 1,000 milliseconds.