How VB Can Determine If Table Is Locked By Other Processes

ID: Q106535

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, version 3.0

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.

MORE INFORMATION

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:
1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a large command button to Form1.
3. 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
   
```
4. 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.
5. Choose Make EXE File from the File menu to create PROGLOC1.EXE.

Make PROGLOC2 by following these steps in Visual Basic:
1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a large command button to Form1.
3. 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
   
```
4. 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.
5. 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.
6. Start PROGLOC2 from Visual Basic by pressing the F5 key. Click the command button to report whether or not a record is locked.

Additional query words: 3.00 row locking multiuser

Keywords :
Version :
Platform :
Issue type :