provided by: 
Originally published at Internet.comRecordset Locking
Recordset locking locks pages of data found in a recordset. By using recordset locking, you can control when and for how long the data is locked. This is different from locking data through bound forms, which gives you little control over the specifics of the locking process.
When you're traversing through a recordset, editing and updating data, locking occurs regardless of whether you intervene, so you must understand when the locking occurs and whether you need to step in to intercept the default behavior. If you do nothing, a record, or possibly an entire page of records, will be locked each time you begin editing data from your VBA code. This record page is 4096 bytes (4K) and surrounds the record being edited. If an OLE object is found in the record being edited, it isn't locked with the record because it occupies its own space.
Pessimistic Locking
VBA lets you determine when and for how long a page is locked. The default behavior is called pessimistic locking, which means that the record or page is locked when the first field is updated. Listing 1 illustrates this process.
Listing 1 - Utilizing Pessimistic Locking Sub PessimisticLock(strAuthorID As String) Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCriteria As String Set cnn = New ADODB.Connection cnn.ConnectionString = " Provider=sqloledb;" & _ "Data Source=(local);Initial Catalog=pubs;uid=sa;pwd=" cnn.Open Set rst = New ADODB.Recordset rst.ActiveConnection = cnn rst.CursorType = adOpenKeyset rst.LockType = adLockPessimistic 'Invoke Pessimistic Locking rst.CursorLocation = adUseServer rst.Open "Select * from Authors Where Au_ID ='" _ & strAuthorID & "'", _ Options:=adCmdText rst!City = "Thousand Oaks" 'Lock occurs here rst.Update 'Lock Released Here End Sub NOTE Not all database providers support all lock types. To determine the functionality available for a particular Recordset object, use the Supports method with adUpdate and adUpdateBatch. Furthermore, the adLockPessimistic setting is not supported when the CursorLocation property is set to adUseClient. Whether the provider does not support the locking type, or the cursor location does not support the locking type, no error results. Instead, the closest available locking type is used...
Read article at Internet.com site