By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Two users editing the same record

P: n/a
A crude, unlikely scenario just so I can get my head around this:

Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.

1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.

At this point I want to inform UserB that the record is locked. How do
I do this?

I have tested OnDirty as suggested by other users and it doesn't seem
to cover this.

Thanks,

Paul
Oct 8 '08 #1
Share this Question
Share on Google+
22 Replies


P: n/a
You don't need code, just settings for this. You must set up the db to use
"edited record" as the default record locking option.

If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).

The user can see if someone else has the record locked by looking at the
symbol. If both users are on the same record and UserA starts to edit, UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. If UserB then starts editing the record, he/she can't
because UserA has it locked. UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).

Chris
Microsoft MVP
pa**@servicequoter.co.uk wrote:
>A crude, unlikely scenario just so I can get my head around this:

Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.

1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.

At this point I want to inform UserB that the record is locked. How do
I do this?

I have tested OnDirty as suggested by other users and it doesn't seem
to cover this.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1

Oct 8 '08 #2

P: n/a
I forgot to say the form needs the record locks property set to edited record,
too.

Chris
Microsoft MVP
Chris O'C wrote:
>You don't need code, just settings for this. You must set up the db to use
"edited record" as the default record locking option.

If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).

The user can see if someone else has the record locked by looking at the
symbol. If both users are on the same record and UserA starts to edit, UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. If UserB then starts editing the record, he/she can't
because UserA has it locked. UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).

Chris
Microsoft MVP
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200810/1

Oct 8 '08 #3

P: n/a
Thanks Chris,

In this particular form the record seletors are hidden. I would
actually prefer to use VBA to trap this with a custom message,
something like:

"The record you are trying to view is locked by another user.
Click OK to view the record as read only or CANCEL to return to the
list..."

I just want to know how to trap the arrival of a user on an edited
record, without having to create my own "IsRecordLocked" boolean
field, or some other solution.

Something like:

Private Sub Form_Current()
If RecordIsBeingEditedBySomeoneElse (Me) then
Do Stuff
End if
End Sub

What code needs to be in the function
RecordIsBeingEditedBySomeoneElse?

Thanks,

Paul

On 8 Oct, 15:52, "Chris O'C via AccessMonster.com" <u29189@uwewrote:
You don't need code, just settings for this. *You must set up the db touse
"edited record" as the default record locking option.

If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).

The user can see if someone else has the record locked by looking at the
symbol. *If both users are on the same record and UserA starts to edit,UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. *If UserB then starts editing the record, he/she can't
because UserA has it locked. *UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).

Chris
Microsoft MVP

p...@servicequoter.co.uk wrote:
A crude, unlikely scenario just so I can get my head around this:
Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.
1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.
At this point I want to inform UserB that the record is locked. How do
I do this?
I have tested OnDirty as suggested by other users and it doesn't seem
to cover this.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2008...
Oct 8 '08 #4

P: n/a
A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy). Each user performs edits locally
- then submits the edits back to the backend using an update query.
This will reduce record locking issues because no one will be working on
the same record simultaneously when performing the edits locally. This
is good for inserts also. Create the record locally then submit it - if
there are keys and referential integrity set - if user1 submits his/her
record before user2 - user2 will get a key violation error.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 8 '08 #5

P: n/a
On Oct 8, 11:40*am, Rich P <rpng...@aol.comwrote:
A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy). *Each user performs edits locally
- then submits the edits back to the backend using an update query.
This will reduce record locking issues because no one will be working on
the same record simultaneously when performing the edits locally. *This
is good for inserts also. *Create the record locally then submit it - if
there are keys and referential integrity set - if user1 submits his/her
record before user2 - user2 will get a key violation error. *

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Oct 9 '08 #6

P: n/a
Doesn't that defeat the whole purpose. Why would we let a second user
edit a record that is already being edited. After User A saves, User
B may very well unwittingly overwrite the User A changes. Or, why
waste the time of UserB making an update if he will only discover,
after the fact, that the record was already open by UserA. I agree
that it would be very convenient to determine if a record is in the
process of being edited in advance. Maybe there is a way to read the
current state of the record selector ? Or some Jet command ?

Greg
On Oct 8, 11:40*am, Rich P <rpng...@aol.comwrote:
A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy). *Each user performs edits locally
- then submits the edits back to the backend using an update query.
This will reduce record locking issues because no one will be working on
the same record simultaneously when performing the edits locally. *This
is good for inserts also. *Create the record locally then submit it - if
there are keys and referential integrity set - if user1 submits his/her
record before user2 - user2 will get a key violation error. *

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Oct 9 '08 #7

P: n/a
On Oct 8, 10:27*am, p...@servicequoter.co.uk wrote:
Thanks Chris,

In this particular form the record seletors are hidden. I would
actually prefer to use VBA to trap this with a custom message,
something like:

"The record you are trying to view is locked by another user.
Click OK to view the record as read only or CANCEL to return to the
list..."

I just want to know how to trap the arrival of a user on an edited
record, without having to create my own "IsRecordLocked" boolean
field, or some other solution.

Something like:

Private Sub Form_Current()
* *If RecordIsBeingEditedBySomeoneElse (Me) then
* * * Do Stuff
* *End if
End Sub

What code needs to be in the function
RecordIsBeingEditedBySomeoneElse?

Thanks,

Paul

On 8 Oct, 15:52, "Chris O'C via AccessMonster.com" <u29189@uwewrote:
You don't need code, just settings for this. *You must set up the db to use
"edited record" as the default record locking option.
If the form has the record selector property set to yes on the form design
(the default), the upper left symbol changes from solid triangle (not locked)
to a pencil (updating record but not committed) to a universal "no!" symbol,
the circle with the diagonal line across it (locked).
The user can see if someone else has the record locked by looking at the
symbol. *If both users are on the same record and UserA starts to edit, UserB
won't see that it's being edited because when he/she arrived on that record
it wasn't locked. *If UserB then starts editing the record, he/she can't
because UserA has it locked. *UserB will get a beep and the symbol will
change to locked ("no!") instead of edit (pencil).
Chris
Microsoft MVP
p...@servicequoter.co.uk wrote:
>A crude, unlikely scenario just so I can get my head around this:
>Split DB; front end back end. Each user has the FE locally the BE is
>stored on a server.
>1. UserA opens customerA's record and edits the telephone number.
>2. UserB opens the same form and tries to navigate to customerA's
>record.
>At this point I want to inform UserB that the record is locked. How do
>I do this?
>I have tested OnDirty as suggested by other users and it doesn't seem
>to cover this.
--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2008...
Here's a simple solution I've tried with some success
when userA opens the record set a field in the record (ie fldEdit) to
a value (ie 1)
if userB tries to open the record test value of fldEdit
If value is 1, deny access to the record, if value is (ie 0), allow
access.
when userA closes the record, reset fldEdit to 0.
Oct 9 '08 #8

P: n/a
Lou O <lg********@gmail.comwrote in
news:54**********************************@c22g2000 prc.googlegroups.com:
Here's a simple solution I've tried with some success
when userA opens the record set a field in the record (ie fldEdit) to
a value (ie 1)
if userB tries to open the record test value of fldEdit
If value is 1, deny access to the record, if value is (ie 0), allow
access.
when userA closes the record, reset fldEdit to 0.
Ouch!

--
lyle fairfield
Oct 9 '08 #9

P: n/a
"Rich P" <rp*****@aol.comwrote in message
news:48***********************@news.qwest.net...
>A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy).
I think not! Network traffic and file size bloat a-go-go!

Oct 9 '08 #10

P: n/a
On 9 Oct, 08:37, "Keith Wilby" <h...@there.comwrote:
"Rich P" <rpng...@aol.comwrote in message

news:48***********************@news.qwest.net...
A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy).

I think not! *Network traffic and file size bloat a-go-go!
So what 'is' the solution folks?

How do I warn a user, with a nice MsgBox, that the record they have
landed on is currently being edited by someone else?

Thanks,

Paul
Oct 9 '08 #11

P: n/a
If you are operating in a corporate environment with multiple users over
the network - Access is not the most ideal tool. I would suggest the
correct tools and methods to implement but this isn't the NG for that.
I would suggest however, that 2 more NG's should be added: one for
small business software tools and another for large business software
tools - two totally different environments. Access would fit into the
small business NG.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 9 '08 #12

P: n/a
If you knew how to check for locked records in vba it would take you a couple
of hours to code and the rest of the day to test/fix, test/fix, test/fix...
because what works for one specific form won't work for all the other
scenarios.

A much better use of your time and skills is to use the RAD environment
Access is designed for and set the record selectors property to yes and train
the users to look for the locked symbol.

Chris
Microsoft MVP
pa**@servicequoter.co.uk wrote:
>In this particular form the record seletors are hidden. I would
actually prefer to use VBA to trap this with a custom message,
something like:

"The record you are trying to view is locked by another user.
Click OK to view the record as read only or CANCEL to return to the
list..."

I just want to know how to trap the arrival of a user on an edited
record, without having to create my own "IsRecordLocked" boolean
field, or some other solution.

Something like:

Private Sub Form_Current()
If RecordIsBeingEditedBySomeoneElse (Me) then
Do Stuff
End if
End Sub

What code needs to be in the function
RecordIsBeingEditedBySomeoneElse?
--
Message posted via http://www.accessmonster.com

Oct 9 '08 #13

P: n/a
"Rich P" <rp*****@aol.comwrote in message
news:48***********************@news.qwest.net...
If you are operating in a corporate environment with multiple users over
the network - Access is not the most ideal tool.
Well now you're either trolling or demonstrating a misunderstanding of what
Access is capable of. A well built application developed using Access *can*
be an ideal tool in such an environment.

Oct 10 '08 #14

P: n/a
I'm not advertising anything - so I'm not trolling. I have been using
Access since Access 2.0. In win 3.11 Access 2.0 was revolutionary - all
the way through Access97 - the first effective 32 bit effor (Acc95 first
32 bit effort). Then the server RDBMS's came on the scene which have
taken over for network based operations. My point is this: for small
business operations - Access is a great tool. Way less overhead than
server based systems - in development/maintenance...

In a large business operation with several users and most likely
webservers - this is where Access has been overtaken by server based
systems. Don't forget - these server based systems came about out of
necessity. MS decided it was logical to separate a file based system
(Access) from the server based system (Sql Server). I suggest use the
right tool for the right job. Small business - use Accesss although sql
server would be fine but a little overkill. Large business - use sql
server - although you could use Access but will be seriously
underpowered.

Even though I have been using Access going on 15 years now, I probably
have not been around the file based scene as long as some die hards
here. But I am also not 25 anymore and I have moved on (although I did
start with DBase3+ in high school). It just seems like the old timers
are afraid of the newer technologies (dagnab them server based systems!)

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 10 '08 #15

P: n/a
pa**@servicequoter.co.uk wrote:
>A crude, unlikely scenario just so I can get my head around this:

Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.

1. UserA opens customerA's record and edits the telephone number.
2. UserB opens the same form and tries to navigate to customerA's
record.

At this point I want to inform UserB that the record is locked. How do
I do this?
Why not just let Access tell the last user to update the record that it's been
updated by another workstation? Yes, this would mean that UserB has done some work
that needs to be discarded but so what given the very unlikely chances of this
happening?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Oct 11 '08 #16

P: n/a
Rich P <rp*****@aol.comwrote:
>A method that would be a little more effective would be to retrieve the
record to be edited from the backend table into a local (temp) table
(for each user's front end mdb copy). Each user performs edits locally
- then submits the edits back to the backend using an update query.
This will reduce record locking issues because no one will be working on
the same record simultaneously when performing the edits locally.
1) This is a lot more work

2) This is hardly ever needed.
>This
is good for inserts also. Create the record locally then submit it - if
there are keys and referential integrity set - if user1 submits his/her
record before user2 - user2 will get a key violation error.
This assumes that there are meaningful keys that can be set to no duplciates. Which
isn't that often with person names or business names just to use one example.
Although others such as Customer Name/Unit number are possible.

When dealing with such data you really need to let a human make the judgement that a
personal or business name already exists in the database or not. Although they
certainly do screw up as well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Oct 11 '08 #17

P: n/a
On Oct 9, 2:56 am, lyle fairfield <lylef...@yah00.cawrote:
Lou O <lgeastw...@gmail.comwrote innews:54**********************************@c22g20 00prc.googlegroups.com:
Here's a simple solution I've tried with some success
when userA opens the record set a field in the record (ie fldEdit) to
a value (ie 1)
if userB tries to open the record test value of fldEdit
If value is 1, deny access to the record, if value is (ie 0), allow
access.
when userA closes the record, reset fldEdit to 0.

Ouch!

--
lyle fairfield

I'll try to expand on Lyle's comment :-). I think that Microsoft
started down that path to the dark side before seeing the light.
Here's an example of the kinds of subtle problems that can come up:

http://msdn.microsoft.com/en-us/magazine/cc163744.asp

Microsoft put a lot of attention into C# to allow allow programs to
take advantange, both now and in the future (for code written today),
of the availability of multiple cores. I.e., your code automatically
runs faster in the future if more cores become available. In addition
to including tools for safe thread locking, they also came up with
some ideas that did not involve locks. Unfortunately, they did not
build these capabilities into any version of Access. I suppose they
wanted SQL Server to harness the parallelism made possible by multiple
cores on a server rather than trying to include those features in
unmanaged code. IMO, the lack of capability for utilizing multiple
cores is the most glaring weakness that Access has at the present
time. Hopefully the article will make you aware of some of the
potential problems before encountering them.

James A. Fortune
CD********@FortuneJames.com
Oct 12 '08 #18

P: n/a
"Rich P" <rp*****@aol.comwrote in message
news:48***********************@news.qwest.net...
>
Even though I have been using Access going on 15 years now, I probably
have not been around the file based scene as long as some die hards
here. But I am also not 25 anymore and I have moved on (although I did
start with DBase3+ in high school). It just seems like the old timers
are afraid of the newer technologies (dagnab them server based systems!)
Hi Rich,

I too work with both Access and Oracle and am aware of the merits of both, I
(wrongly, so it seems) inferred from your post that you were suggesting that
Access was no good in all aspects of the corporate world, whereas my
experience would indicate to the contrary. It never ceases to amaze me how
powerful a file-based system such as Access can be and it is more than
adequate for a lot of our corporate needs, although I do recognise the
limitations. I hope it's not an age related issue ...

Regards,
Keith.

Oct 13 '08 #19

P: n/a
Hi Keith,

I am in total agreement with you. But as James A Fortune (and Lyle -
less directly) point out - kind of what I try to point out - is that the
sphere of influence of Access has changed in the more modern times of
RDBMS. I think My problem is that my gift of communicating my ideas has
some limitations. Having been a (nerd) during my educational years I
have focused most of my energy trying to solve differential equations
and writing code (things haven't changed much in my post educational
years :). Thus, my efforts to share my tech experiences in these NGs
may not come off as strongly as desired.

Access is still a great tool - and unlike DBase3+ - it is still around.
But I do feel that server based RDBMS's have overtaken Access in the
Corporoate environment. So I try to suggest strategies that would
leverage the strengths of Access - which for now I would say is reducing
overkill for the small business. But for the large businesses - I would
say that the additional horsepower you get from the server based system
is pretty hard to discount.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 13 '08 #20

P: n/a
On Oct 12, 12:59*am, CDMAPos...@fortunejames.com wrote:
On Oct 9, 2:56 am, lyle fairfield <lylef...@yah00.cawrote:
Lou O <lgeastw...@gmail.comwrote innews:54**********************************@c22g20 00prc.googlegroups.com:
Here's a simple solution I've tried with some success
when userA opens the record set a field in the record (ie fldEdit) to
a value (ie 1)
if userB tries to open the record test value of fldEdit
If value is 1, deny access to the record, if value is (ie 0), allow
access.
when userA closes the record, reset fldEdit to 0.
Ouch!
--
lyle fairfield

I'll try to expand on Lyle's comment :-). *I think that Microsoft
started down that path to the dark side before seeing the light.
Here's an example of the kinds of subtle problems that can come up:

http://msdn.microsoft.com/en-us/magazine/cc163744.asp

Microsoft put a lot of attention into C# to allow allow programs to
take advantange, both now and in the future (for code written today),
of the availability of multiple cores. *I.e., your code automatically
runs faster in the future if more cores become available. *In addition
to including tools for safe thread locking, they also came up with
some ideas that did not involve locks. *Unfortunately, they did not
build these capabilities into any version of Access. *I suppose they
wanted SQL Server to harness the parallelism made possible by multiple
cores on a server rather than trying to include those features in
unmanaged code. *IMO, the lack of capability for utilizing multiple
cores is the most glaring weakness that Access has at the present
time. *Hopefully the article will make you aware of some of the
potential problems before encountering them.

James A. Fortune
CDMAPos...@FortuneJames.com
The link you posted didn't work.
However, I realize my solution is a somewhat simplistic approach and
prone to some problems.
The one I mostly encounter is a user exiting the record improperly
thus not re-setting the edit field.
To overcome that I provide a reset button on the form. Of course this
doesn't guarantee someone else has not
opened the record in the meantime.
But like Tony says somewhere else in this thread; "so what, given the
very unlikely chances of this
happening?"
And if it happens I still have the Form_Error event to fall back on.
Oct 15 '08 #21

P: n/a
On Tue, 14 Oct 2008 19:17:15 -0700 (PDT), Lou O <lg********@gmail.comwrote:

>But like Tony says somewhere else in this thread; "so what, given the
very unlikely chances of this happening?"
A very wise old man said, "If you don't make something dammed fool proof,
pretty soon you are going to find one."

Chuck

Oct 15 '08 #22

P: n/a
On Oct 14, 10:17 pm, Lou O <lgeastw...@gmail.comwrote:
The link you posted didn't work.
That's strange. I'm sure I tried the link before I posted it. Maybe
try the wayback machine -- seriously.

I'm not trying to bust your fun Dude. Access is a great environment
for learning about record locking, transactions, etc. and I love it
dearly, but Microsoft seems to be deliberately placing Access in a
position that is significantly weaker than it was in just a few years
ago. If you get something that works for you, that's great. By
warning you about the problems Microsoft encountered I was hoping to
keep you from getting some recondite error in the future that could
have been prevented.

James A. Fortune
CD********@FortuneJames.com
Oct 17 '08 #23

This discussion thread is closed

Replies have been disabled for this discussion.