When to Use Freelocks in a Multi-User Environment (122958)

MORE INFORMATION

The database engine in Microsoft Visual Basic relies on background processing to keep all records current in a recordset and to remove read locks. Usually, read locks are removed and data in local dynaset objects is updated only when no other actions (including mouse moves) are occurring. If data processing is so intense that normal background management of locks is unable to keep up, you may encounter various lock errors, usually Error 3186:

or Error 3260:

Certain operations, such as using a Seek method on a Table object, temporarily lock a recordset and prevent others from accessing the table. Performing a Seek places a read lock on the index while the seek is performed. After the Seek is finished, if the database engine is not allowed time to release its read lock, other users may be locked out from manipulating the table.

This is where the FreeLocks statement comes in handy. According to the Visual Basic online Help, the FreeLocks statement "suspends data processing, allowing the database to release locks on record pages and make all data in your local Dynaset objects current in a multiuser environment."

In addition to FreeLocks, if your program is tying up Windows and the CPU, you should follow the FreeLocks command with a DoEvents statement. FreeLocks allows the database engine to release its read locks; DoEvents allows Windows to catch up on processing background tasks such as repainting portions of the screen or processing user input. DoEvents should be used carefully, however, because it allows your VB code to be reentrant.

You will want to implement the FreeLocks statement after the following operations:

• Opening Recordsets: Using CreateDynaset and CreateSnapShot, places a read lock on the table while the object is initialized. For example:

     Dim db As Database, ds As DynaSet
     Set db = OpenDatabase("Bibio.MDB")
     Set ds = db.CreateDynaset("Select * From Authors")
     FreeLocks
  
  						

• Searching: Use FreeLocks after performing a Seek, FindFirst, FindPrevious, FindNext or FindLast. Searches place a read lock on an index, if available, or on the table while the search is performed. For Example:

     Dim db As Database, tb As Table
     Set db = OpenDatabase("Biblio.MDB")
     Set tb = db.OpenTable("Titles")
     tb.Index = "Au_ID"
     tb.Seek "=", 6
     FreeLocks
  
  						

• Deleting and Updating records: Performing a Delete or Update on a recordset locks the page that the current record is on while the record is removed or added to the table. Use FreeLocks immediately following the Update. For example:

     ds.Edit
     ds("Favorite Music").Value = "Jazz"
     ds.Update
     FreeLocks
  
  						

• Error Events: If you implement error trapping in your program, you should add a FreeLocks statement in the On Error event to handle the case where a database error occurs. For example:

     Error_Handler:
        If (Err = 3186) Or (Err = 3260) Then   'Currently Locked DB Errors
  
           FreeLocks
           ...
        End If
        Resume
  
  						

NOTE: while it is useful to implement the FreeLocks statement, you must be careful not to implement too many of them. Overuse of FreeLocks may result in poor performance.