Hold it there nomus1: you're dealing with users here, so it's not so obvious that they will close the db in the way you expect. Furthermore: if the db is on a network, and the network connection fails the "lock" record also remains in the db
a way to actually do this is to make a workgroup file, provide every user with a logon name, and upon opening the database, checking if the user is already logged on to the workgroup file. I did not test in this exact way (i use it to count users that are logged in ) so it might need some minor adjustments:
Dim cn As New Connection
Dim rs As New Recordset
Dim var_user As Variant
Dim var_val As Variant
Dim str_db As String
str_db = CurrentDb
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & str_db
Set rs = cn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Do While Not rs.EOF
var_val = rs.Fields(1).Value
If InStr(var_val, vbNullChar) > 0 Then 'eliminate NULLS in the string
var_val = Left(var_val, _
InStr(var_val, vbNullChar) - 1)
End If
var_user = var_val
If var_user = CurrentUser Then
MsgBox "too bad, get out !!"
'' handle kick-out
Exit Do
End If
rs.MoveNext
Loop
hope this does the trick
cpuburn
)
.