The information in this article applies to:
- Professional Edition of Microsoft Visual Basic 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:
a. Start a new project in Visual Basic. Form1 is created by default.
b. Add a large command button to Form1.
c. 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
d. 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.
e. Choose Make EXE File from the File menu to create PROGLOC1.EXE.
- Make PROGLOC2 by following these steps in Visual Basic:
a. Start a new project in Visual Basic. Form1 is created by default.
b. Add a large command button to Form1.
c. 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
d. 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.
e. 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.
f. Start PROGLOC2 from Visual Basic by pressing the F5 key. Click the
command button to report whether or not a record is locked.