lock a record and keep locked

[Guest]

How do I lock a single record and keep it locked until the application closes.
I need to allow reads only but generate a trapable error if an attempt to
edit or delete accurs. The system is set to lock edit.
It is to be used in a user table so only 1 front end can access the users
table at a time.
I dont want to use a field in this record as i have had problems before with
crashes etc.
The table is not used in a form.
I use currentdb and DAO recordsets. I dont know how to keep it locked.
Thank you for any help.

 

[Alex Dybenko]

hi,
you can open recordset with that record using dbPessimistic lock option, run
edit method and keep it like this

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

It dosn't seem to work. I assume you meant do rst.Edit then exit the
function. I tried it all ways as you will see below.
One thing strange Access (XP version) would not let me put the dbPessimistic
in the Lock position only the Options I seem to remember it demanded the same
with dbReadOnly.
Both front ends went through that function without error. I checked to make
sure they are both using the same table.
Any ideas??

Function CheckUserLock(varUserName As Variant) As Boolean
Dim rst As DAO.Recordset, strSql As String
On Error GoTo er
strSql = "SELECT * FROM UserDetails WHERE ((Name)=" & Chr(34) &
varUserName & Chr(34) & ");"
Set rst = CurrentDb.OpenRecordset(strSql, , dbPessimistic)
rst.Edit
'rst![normal] = Not rst![normal]
'rst.Update
CheckUserLock = True
Exit Function
er:
CheckUserLock = False
Set rst = Nothing
End Function

 

[Guest]

I did more testing. If the program is stopped before the exit function it
works.
As soon as an update or the end function the lock is lifted.
how is the lock kept on??

 

[Alex Dybenko]

Hi,
yes, my idea was the same, but you have to declare rst at module level. but
idea was wrong, this locks the whole table. Let me try something else, and I
will come back to you


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

It dosn't seem to work. I assume you meant do rst.Edit then exit the
function. I tried it all ways as you will see below.
One thing strange Access (XP version) would not let me put the
dbPessimistic
in the Lock position only the Options I seem to remember it demanded the
same
with dbReadOnly.
Both front ends went through that function without error. I checked to
make
sure they are both using the same table.
Any ideas??

Function CheckUserLock(varUserName As Variant) As Boolean
Dim rst As DAO.Recordset, strSql As String
On Error GoTo er
strSql = "SELECT * FROM UserDetails WHERE ((Name)=" & Chr(34) &
varUserName & Chr(34) & ");"
Set rst = CurrentDb.OpenRecordset(strSql, , dbPessimistic)
rst.Edit
'rst![normal] = Not rst![normal]
'rst.Update
CheckUserLock = True
Exit Function
er:
CheckUserLock = False
Set rst = Nothing
End Function

hi,
you can open recordset with that record using dbPessimistic lock option,
run
edit method and keep it like this

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Another update
It locks a "page" I think 2000 bytes. If I add a Dummy Memo field (there is
only a few records) it works fine. The other way is to change it all to a
random file and use the lock and unlock. It looks like to much work and the
other way is working

Thanks a lot for your help

Hi,
yes, my idea was the same, but you have to declare rst at module level. but
idea was wrong, this locks the whole table. Let me try something else, and I
will come back to you


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

It dosn't seem to work. I assume you meant do rst.Edit then exit the
function. I tried it all ways as you will see below.
One thing strange Access (XP version) would not let me put the
dbPessimistic
in the Lock position only the Options I seem to remember it demanded the
same
with dbReadOnly.
Both front ends went through that function without error. I checked to
make
sure they are both using the same table.
Any ideas??

Function CheckUserLock(varUserName As Variant) As Boolean
Dim rst As DAO.Recordset, strSql As String
On Error GoTo er
strSql = "SELECT * FROM UserDetails WHERE ((Name)=" & Chr(34) &
varUserName & Chr(34) & ");"
Set rst = CurrentDb.OpenRecordset(strSql, , dbPessimistic)
rst.Edit
'rst![normal] = Not rst![normal]
'rst.Update
CheckUserLock = True
Exit Function
er:
CheckUserLock = False
Set rst = Nothing
End Function

hi,
you can open recordset with that record using dbPessimistic lock option,
run
edit method and keep it like this

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

How do I lock a single record and keep it locked until the application
closes.
I need to allow reads only but generate a trapable error if an attempt
to
edit or delete accurs. The system is set to lock edit.
It is to be used in a user table so only 1 front end can access the
users
table at a time.
I dont want to use a field in this record as i have had problems before
with
crashes etc.
The table is not used in a form.
I use currentdb and DAO recordsets. I dont know how to keep it locked.
Thank you for any help.