Microsoft Office 2000/Visual Basic Programmer's Guide

Multiuser Settings

To control how Microsoft Jet behaves in a multiuser environment, you can use a variety of Windows registry entries. You can change these entries by using the Registry Editor (Regedit.exe for Windows 95 and Windows NT Workstation 4.0) to edit values in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0 subkey for Jet-specific settings, or in HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\9.0\Access\Settings for Access-specific settings. The following settings are also available in the Access user interface, either on the Advanced tab of the Options dialog box (Tools menu), or programmatically through the SetOption method of the Access Application object.

• Default open mode   Determines how the database is to be opened if no explicit options are specified. You can specify explicit options when you open the database directly, through the DAO OpenDatabase method or the ADO Open method (Connection object). If the database is to be used in a multiuser environment, set this option to Shared. This setting corresponds to the Default Open Mode for Databases entry in the Access Settings subkey in the registry.
  
  
• Default record locking   Specifies how records are to be locked when they are edited from table and query datasheets. This setting is also inherited as a default setting for the RecordLocks property when you create a new form, but you can override this setting for any form in the database by setting the form’s RecordLocks property yourself. The three settings correspond to the locking modes that Microsoft Jet defines. No Locks corresponds to optimistic locking, Edited Record corresponds to pessimistic locking, and All Records corresponds to recordset locking. This setting corresponds to the Default Record Locking entry in the Access Settings subkey in the registry.
  
  
• Number of update retries   Specifies how many times Access tries to save a changed record that is locked before displaying an error message. This sets the Number of Update Retries entry in the Access Settings subkey in the registry.
  
  
• Update retry interval   Specifies the number of milliseconds Access waits before trying again to save a changed record that is locked by another user. This setting corresponds to the Update Retry Interval (msec) entry in the Access Settings subkey in the registry.
  
  
• ODBC refresh interval   Specifies how often (in seconds) Access refreshes a form with updates to the current set of records from Open Database Connectivity (ODBC) data sources. The refreshed data doesn't show new records added by other users, or remove records deleted by other users. To display all updates, additions, and deletions, you must requery the form by pressing SHIFT+F9, or you can use the Requery method from code. This setting corresponds to the ODBC Refresh Interval (sec) entry in the Access Settings subkey in the registry.
  
  
• Refresh interval   Specifies how often (in seconds) Access refreshes a form with updates to the current set of records from native Microsoft Jet data sources. The refreshed data will not show new records added by other users, or remove records deleted by other users. To display all updates, additions, and deletions, you must requery the form by pressing SHIFT+F9, or you can use the Requery method from code. It should be noted that this setting performs the same function as the PageTimeout entry for Microsoft Jet in the registry. In Access, both the RefreshInterval and the PageTimeout entry are in effect at the same time. This setting corresponds to the Refresh Interval (sec) entry in the Access Settings subkey in the registry.
  
  
• Open databases using record-level locking   Specifies whether record-level or page-level locking mode is used when opening databases on a per-user basis. For more information, see "Page-Level Locking vs. Record-Level Locking" later in this chapter. This setting corresponds to the Use Row Level Locking entry in the Access Settings subkey in the registry.

For the most flexibility, you can temporarily modify Microsoft Jet engine – specific settings in VBA code by using the DAO SetOption method of the DBEngine object, or from ADO by setting Microsoft Jet 4.0 OLE DB Provider-specific session properties of the Connection object. For information about the DAO SetOption method, search the DAO Help index for "SetOption method." For information about setting ADO provider-specific session properties, see ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.