Editing and Locking records in an Access 2003 project with SQL Ser

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We are using a Microsoft Access 2003 project connected to a SQL Server 2000
back end. It is multi-user, about 5 users have their own copies of the .adp
and they all have rights to add and edit data. Al of the .adp projects are
connected to the same SQL Server back end.

I have a form frmClients and its record source property is saved as “Select
* From tblClients where 1 = 2†(this is so the form doesn’t come up with
data)

A combo box lists the clients, and when it is updated, the AfterUpdate event
of the combo box sets the form’s recordsource to whichever client was
selected. For example it might set the recordsource for frmClients to
“Select * From tblClients where ClientID = 37â€

Now let’s say another user is already editing Client 37. I am trying to get
it so that the 2nd user who is trying to get into edit Client 37 receives a
message telling him/her that “User ___ is already editing the client you have
chosen, so you are not allowed to edit this client at the same time.â€

So, the questions are:

1) How can I get the code to check to see if anyone else is currently
editing Client 37?

And

2) If someone is editing Client 37, how can I get the code to figure out who
is currently editing Client 37?

This seems possible, but I just don’t know how to do it! Thanks in advance
for any help you can give. Peace.
 
Hi wilbur88,

From your descriptions, I understood that you would like to get notified
who is locked the record and then other users will not able to modify it
further. Have I understood you? Correct me if I was wrong.

I am looking forword to this issue and I will try to generate a sample with
Northwind database. However, I would like to set your expectation that it
won't be a easy job to do so and we might not be able to get who is locking
the data.

Thank you for your patience and corporation.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
ghts.
 
Check the LockEdits property of the form.

I don't have Access here to check, so I can't remember what the values
of that property are. However, there are two different locking methods
available via that property:

- "Optimstic" locking. Both users are allowed to edit the same record
at the same time, but only the first user will be able to save his
edits. The second user will get an error, when he tries to save his
edits.

- "Pessimistic" locking. The frst user who starts to edit the record,
will get a lock on it. The second user who tries to edit that record,
will get an error, even if the first user has not saved his edits yet.

Otimistic locking is good when there is little chance of edits
conflicting. It is bad because the second user gets to type all of his
changes *before* he's told that he can't save them.

Conversely, pessimistic locking is good when there is a high chance of
coflicting changes. It prevents the second user wasting his time by
typing changes that can't be saved. But a downside is that the first
user can start to edit (thus locking the record), then go to lunch
without saving it - thus leaving that record locked-out to everyone
else.

As for who has the record locked, "who cares" wth optimistioc locking -
the message shuld just tell the user to requery the record & try again.
It only matters with pessimisic locking, becase you need to know who's
gone to lunch & left the record locked.

Remember that in Access 97, all record lockng is on a page basis; when
a record is locked, all other records in the same 2kb page of data are
also locked. That might be anything from zero to dozens of other
records, depending on the record sizes.

In a2k(?)+ there is an option for achieving true record-level locking.

HTH,
TC
 
Hi wilbur88,

Sorry for the delay as I found TC had a splendid answer with sufficient
information. I wanted to post a quick note to see if you would like
additional assistance or information regarding this particular issue.


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top