Record locking in vba

Question

i have a VBA application which is spitted into two one for front end; and backend while updating how to prevent two users edit it ??

Answer 1

Yoy may try to invoke pessimistic locking

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

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 Table Where ID ='" _
    & strID & "'", _
    Options:=adCmdText

rst!Name = "New name" 'Lock occurs here

'... when it is locked, you may do other operations

rst.Update 'Lock Released Here

You will have to implement error handling, because when 2nd client wants to edit and cannot lock the record during timeout, error will be raised.

However pessimistic locking is not the best scenario, I would think about optimistic locking and either First Wins or Last Wins strategy

Here is an online book Alison Balter's mastering Microsoft Access 2000 development, it should help you.

Answer 2

Access has locking. From Access Help:

Specify the locking level used in an Access database in a multiuser environment

On the Tools menu, click Options.

Click the Advanced tab.

To make record-level locking the new default setting for the current Microsoft Access database, select the Open databases using record-level locking check box.
To make page-level locking the new default setting for the current Access database, clear the Open databases using record level locking check box.

Notes

This setting takes place the next time you open the Access database, but you must use the Open command on the File menu rather than the list of most recently used files at the end of the File menu. This behavior is the same as the setting for the default open mode.

If you select Open databases using record level locking, this becomes the default behavior for access to data through a form, a datasheet, and code that uses a recordset object to loop through records, but not through action queries, nor through code that performs bulk operations using SQL statements. For more information, see Chapter 16, "Multiuser Database Applications," in the Microsoft Office 2000/Visual Basic Programmer's Guide.