How to know if anyone using an access sql application is in a reco

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hi,
I need to delete a record from an admin menu. This can be achieved using dao
and sql code. However, I would first like to check if the record to be
deleted is being used by any of the application user

Is there any way to check if the main form is being utilized by a user using
this record. If so what would be the approach to determine so. I would not
like to delete a record from admin menu if someone is using the record. I
appreciate any help for resolution of this issue. Thanks
 
In the form where a user might be working with the row, you can set the
form's Record Locks property (on the Data tab of the form's property sheet)
to Edited Record. That only locks the row if a user is editing it. I don't
know any builtin features to lock a single row just being viewed. Maybe you
could set the Record Locks property to All Records, and use a Record Source
something like Select * From MyTable Where pkID = 365. That would just load
a single row at a time into the form. You'd have to give the user something
like a combo box or text box to select the row they want to see, and in the
AfterUpdate event you could update the form's record source to show that one
row.
 
A word of warning.

I have attempted to use edited record for record locking. However as a
consequence records that are "near" the edited record may also be
locked since in reality it is the "Page" of data that is being locked.
This gets frustrating in a high user application.

What I have ended up using is simply "No Locks".
This class of lock is somewhat misleading, because the following will
happen.
If two users are on the same record, the first user to change the
record has in reality locked the record. You can see this on the
second users record selector as a slashed-circle. Until such time as
the first user gets off of and/or saves the record, the second user
will get a warning message if he/she tries to change the record and
save it. Only one user's updates will be saved, depending on the
responce to the error message. Not the best, but better than what was
happening with the edited record method.

This was the acceptable solution for me, since I have sometimes up to
20 users in the application at a time, BUT they are NOT trying to
update "random" records but only those assigned to them. This means we
hardly ever will have the lock out situation. When I was trying to use
"Edited Record lock" we were continually getting the lockout situation
on "close" records.

Ron
 
Depending on the Access version, I think there's an Access option to specify
a default of page-level locking, which exhibits the behavior you describe,
or record-level locking which should work correctly in this situation.
Whenever I have tested the No Locks setting, it does what it says and 2
users can independently update the same record at the "same" time, leading
to lost updates for one of the users.

In Access 2007, the setting is on the Advanced pane of the Options, in the
Advanced section, labeled 'Open databases by using record level locking'.
 
I may try again once we are all on Access 2007.

So far any time I have had two people on the same record I have gotten
the error message. However that does leave us with the possibility
that the second person say to do the update and that wipes out the
first peron's work. But our environment does NOT lend itself to two
people working on the same record at the same time.

Ron
 
Back
Top