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.
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:
- 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.
- Make PROGLOC2 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 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
- 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.
- 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:
- 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: KB106535