Microsoft KB Archive/106535

= How VB Can Determine If Table Is Locked By Other Processes =

Article ID: 106535

Article Last Modified on 10/20/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q106535



SUMMARY
This article describes how to detect if a database table has any records locked by other users or processes. If you open the table with the options to deny read and write access, a trappable error will indicate that other users or processes are using the table. This information is useful for managing tables in a multiuser or network system.



Step-by-Step Example
The following example uses one program (PROGLOC1.EXE) to optionally lock a record in a table. A separate, concurrent program (PROGLOC2) checks to see if any records in the table are currently locked.

 Make PROGLOC1.EXE by following these steps in Visual Basic:

 Start a new project in Visual Basic. Form1 is created by default. Add a large command button to Form1.  Enter the following code for the Command1_Click event procedure: Sub Command1_Click Dim db As database Set db = OpenDatabase("biblio.mdb") Dim ds As dynaset Set ds = db.CreateDynaset("authors") ds.Edit    ' Locks the first record in the dynaset. MsgBox "First record in dynaset is locked. Press OK to unlock." command1.Caption = "record now unlocked" End Sub  Choose Save File As from the File menu, and save as PROGLOC1.FRM.

Choose Save Project As from the File menu, and save as PROGLOC1.MAK. Choose Make EXE File from the File menu to create PROGLOC1.EXE.</ol> </li> Make PROGLOC2 by following these steps in Visual Basic:

<ol style="list-style-type: lower-alpha;"> Start a new project in Visual Basic. Form1 is created by default.</li> Add a large command button to Form1.</li>  Enter the following code for the Command1_Click event procedure: Sub Command1_Click

Dim db As database Set db = OpenDatabase("biblio.mdb") Dim tb As table ' See if table has locks by opening and denying others Read/Write: On Error Resume Next Set tb = db.OpenTable("authors", 3) ' 3 = Deny Read & Write (2+1) If Err = 0 Then command1.Caption = "not locked" Else command1.Caption = "locked due to err=" & err End If        tb.Close ' If no error here you could reopen table without denying access.

End Sub </li> Optional steps to save this sample program: Choose Save File As from the File menu, and save as PROGLOC2.FRM. Choose Save Project As from the File menu, and save as PROGLOC2.MAK.</li> Run PROGLOC1.EXE from Windows File Manager and click the command button to lock a record. Leave the following message box up without pressing OK:

First record in dynaset is now locked. Press OK to unlock.

</li> Start PROGLOC2 from Visual Basic by pressing the F5 key. Click the command button to report whether or not a record is locked.</li></ol> </li></ol>

Additional query words: 3.00 row locking multiuser

Keywords: KB106535

-

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

© Microsoft Corporation. All rights reserved.