| Microsoft Office 2000/Visual Basic Programmer's Guide | |
When a user edits a record in a shared database, you can prevent conflicts with other users by locking the data while it is being edited. When data is locked, any number of users can read it, but only one user can make changes to it.
In previous versions of the Jet database engine (version 3.5x and earlier), locking a record locks one page of data. For previous versions of the Jet database engine, a page is equal to 2K (2048 bytes) of data within the database file; for the current version, Jet 4.0, a page is 4K (4096 bytes) of data. (The size was doubled to accommodate storing data as Unicode characters, which occupy 2 bytes instead of the 1 byte used by previous characters.) Locking at the page-level can lock multiple records if the combined length of two or more records is smaller than the size of a page; this prevents other users from editing any records on that page until the user is finished editing the record that caused the entire page to be locked. Page locking generally results in better performance, but can reduce the ability of other users to edit data.
A new feature of Jet 4.0 is the ability to lock individual records rather than pages. In Access, this is controlled by the Open databases using record-level locking setting on the Advanced tab of the Options dialog box (Tools menu). By default, this setting is selected (on), which means two users can update or delete two different records that are located on the same page within the database (which isn't possible when you use pessimistic locking under page-level locking). The locking mode that is in effect is determined by the first user to open a database. If the first user has the Open databases using record-level locking setting selected, all users who subsequently open that database will use record-level locking whether they have the setting selected or not. Conversely, if the first user to open a database has the Open databases using record-level locking setting cleared, all users who subsequently open that database will use page-level locking.
When record-level locking is on, data edited through Access forms and datasheets will use record-level locking. Also, Recordset objects opened by using the DAO OpenRecordset method, and any ADO methods (when you use the Microsoft Jet 4.0 OLE DB provider) that open or return a Recordset object will use record-level locking. However, any SQL Data Manipulation Language (DML) queries — that is, queries that add, delete, or modify records — that are run from ADO (when you use the Microsoft Jet 4.0 OLE DB Provider), DAO, or the Access query user interface will use page-level locking. Page-level locking is used for SQL DML statements to improve performance when you are working with many records. However, even when record-level locking is turned on, it is not used for updates to values in memo fields and values in fields that are indexed — they still require page-level locking.
You can't programmatically override record locking settings for DAO Recordset objects, Access forms and datasheets, or SQL DML statements run from the Access query user interface.
When you use ADO with the Microsoft Jet 4.0 OLE DB Provider to work with an Access database, you can set the provider-specific Jet OLEDB:Database Locking Mode property of the Connection object before opening a database, and then set the provider-specific Jet OLEDB:Locking Granularity property of the Recordset object used to execute SQL DML statements or to work with methods that open or return a Recordset object. For information about the Jet OLEDB:Database Locking Mode and Jet OLEDB:Locking Granularity properties, see ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.