Microsoft KB Archive/122958

= When to Use Freelocks in a Multi-User Environment =

Article ID: 122958

Article Last Modified on 10/28/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q122958



SUMMARY
This article explains when and where to use the FreeLocks statement in your database program to prevent unintentional read locks.



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:

Couldn't save; currently locked by user '' on machine ''.

or Error 3260:

Couldn't update; currently locked by user '' on machine ''.

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

</ul>

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.

Additional query words: 3.00

Keywords: KB122958

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.