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

Warn user-2 if user-1 on requested record

P: n/a
Using Access 2003 and using a form that's mostly bound. I need a way
to tell if user-1 is on the record when user-2 tries to open the same
record, w/o waiting for the user-1 to save the record first. The only
way I can think of is by adding a flag to the record and setting it to
true when user-1 opens the record. If user-2 tries to open it, the
flag is checked for true, then a message would be displayed to them
that user-1 is on the record. When user-1 saves the record, then the
flag is set to false.

Is this a good way to do this? Thanks for any help or advice.
Sep 16 '08 #1
Share this Question
Share on Google+
19 Replies


P: n/a
No, that's what record locking is for. Change from "no locks" to "edited
record". The record will be locked for all users who try to edit the record
once another user starts editing. The lock will be released after the first
user saves the record.

Chris
Microsoft MVP
em******@kumc.edu wrote:
>Is this a good way to do this?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200809/1

Sep 16 '08 #2

P: n/a
On Sep 16, 11:17*am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:
No, that's what record locking is for. *Change from "no locks" to "edited
record". *The record will be locked for all users who try to edit the record
once another user starts editing. *The lock will be released after the first
user saves the record.

Chris
Microsoft MVP

emann...@kumc.edu wrote:
Is this a good way to do this?

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2008...
I have "Edited Record" selected already. Some more info on this
database.....it's a split database connected to SQL Server 7. The
form is a mixture of bound and unbound fields. The user selects a
patient from a combobox which then refreshes the form with the
patient's info.

I appreciate your reply. Let me know if there's anything else I can
tell you about the database.

Sep 16 '08 #3

P: n/a
I don't think you'll see any effect on the SQL Server tables when "edited
record" is selected, because it's a setting that applies to Jet tables if the
db was opened with row level locking set.

You can control record locking when you create recordsets with the
adLockPessimistic and dbPessimistic attributes, but that isn't going to help
with the bound forms - so you may want to reconsider your design. Depending
on your SQL Server settings and how your SQL Server database structure is
designed, you may be able to control record locking with the isolation level.
See the BOL for ideas and examples.

If your tables are designed for pessimistic locking, you can create a
clustered index on a column that will make it more likely popular rows (read:
high traffic) are scattered throughout the table instead of sequentially to
help avoid contention due to updates and inserts on the same data page. You
can reduce the fillfactor on indexes on tables for the same effect.

Chris
Microsoft MVP
em******@kumc.edu wrote:
>On Sep 16, 11:17*am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:
>No, that's what record locking is for. *Change from "no locks" to "edited
record". *The record will be locked for all users who try to edit the record
[quoted text clipped - 9 lines]
>--

I have "Edited Record" selected already. Some more info on this
database.....it's a split database connected to SQL Server 7. The
form is a mixture of bound and unbound fields. The user selects a
patient from a combobox which then refreshes the form with the
patient's info.

I appreciate your reply. Let me know if there's anything else I can
tell you about the database.
--
Message posted via http://www.accessmonster.com

Sep 16 '08 #4

P: n/a
Greetings,

I will guess you are using ODBC as your connection source to the Sql
server and operating the linked tables the same as if they were Access
tables. This is fine and doable. But as you are seeing/experiencing -
there are limitations to operating in this manner - particularly in a
multi-user environment.

I went through a phase where I started becoming somewhat critical of
Access as a front end to sql server, but I have lightened up on that.
Here is a workaround suggestion if you are using an mdb (or acc whatever
they are calling Access2007 DB's now if you are on Acc2007).

You can sort of treat the mdb like a .Net system where you pull a
record(s) from the server table to a local table - perform your
edit/update operations locally, and then re-submit the local record back
to the server table using DAO (if you want to keep everything local) or
ADO (which in my experience shows more performance less contention
issues). This method simulates the disconnected paradigm of .Net even
with ODBC tables (which are continuously connected to the mdb). It
reduces dead locking contention issues significantly.

Assuming the server tables have referential integrity constraints in
place you won't have to worry about duplicate entries and other
contention issues with this method.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Sep 16 '08 #5

P: n/a
Rich P <rp*****@aol.comwrote in news:12*************@news.newsfeeds.com:
You can sort of treat the mdb like a .Net system where you pull a
record(s) from the server table to a local table - perform your
edit/update operations locally, and then re-submit the local record back
to the server table using DAO (if you want to keep everything local) or
ADO (which in my experience shows more performance less contention
issues). This method simulates the disconnected paradigm of .Net even
with ODBC tables (which are continuously connected to the mdb). It
reduces dead locking contention issues significantly.
Sequence of events:

1. Mary pulls the record to her local table;
2. Fred pulls the record to his local table;
3. Mary edits the record;
4. Mary submits her edited record to the server which saves it;
5. Fred edits his copy of the record, which does NOT have Mary's changes,
because he pulled it before Mary did her save;
6. Fred submits his edited record to the server which saves it.

What happens to Mary's changes?

--
-
lyle fairfield
Sep 16 '08 #6

P: n/a
On Sep 16, 4:15*pm, lyle fairfield <lylef...@yah00.cawrote:
Rich P <rpng...@aol.comwrote innews:12*************@news.newsfeeds.com:
You can sort of treat the mdb like a .Net system where you pull a
record(s) from the server table to a local table - perform your
edit/update operations locally, and then re-submit the local record back
to the server table using DAO (if you want to keep everything local) or
ADO (which in my experience *shows more performance less contention
issues). *This method simulates the disconnected paradigm of .Net even
with ODBC tables (which are continuously connected to the mdb). *It
reduces dead locking contention issues significantly.

Sequence of events:

1. Mary pulls the record to her local table;
2. Fred pulls the record to his local table;
3. Mary edits the record;
4. Mary submits her edited record to the server which saves it;
5. Fred edits his copy of the record, which does NOT have Mary's changes,
because he pulled it before Mary did her save;
6. Fred submits his edited record to the server which saves it.

What happens to Mary's changes?

--
-
lyle fairfield

Thanks for your replies.

Sep 17 '08 #7

P: n/a
This all depends on the Ref-Int and key setup. If the data entry people
have guidelines to follow and one person submits their entries before
the other- the 2nd person will get a key violation error -
theoretically

Here is where I start getting critical of Access as a front end to sql
server - slice it anyway you want - but Access just cannot trap most of
the error messages from the sql server. This observation is based on my
experience and hours upon hours of trying to figure out why we were
having data entry problems til we migrated to .Net which is much better
at catching all sql server errors.

On the flip side, since Access has the limitation of not being able to
catch all of the sql server errors - I guess it doesn't matter what
method is used except - be careful!

Access is actually fine as a front end for data entry to a sql server
table - but for error trapping and enforcing data entry rules - this is
the part where I have had problems using Access as a front end against a
sql server. And the OP is having the same problems.

Access is a great tool, but against a sql server - that changes.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
--
Posted Via Newsfeeds.com Premium Usenet Newsgroup Service
----------------------------------------------------------
http://www.Newsfeeds.com

Sep 17 '08 #8

P: n/a
In theory, if referential integrity is set and keys are set then if
user1 edits/updates data this user will get an error message from the
sql server if the business rules are not met - usually some key
violation error. Lets say user1 edits/updates data and then user2
edits/updates the same data 1 millisecond after user1 using the method I
suggested. If user2 is following the company's business rules and
enters the same data then this user will likely get some key violation
message of duplicate data - the record already exists. But if user2 is
actually changing the data that User1 just edited - meaning user2 came
along after user1 - say an hour later - it doesn't matter if my method
is used or if User2 edits the data directly in the table.

The problem with Access here is that Access just can't read most of the
error messages from the sql server. It just can't. Slice it/dice it -
argue till you are blue in the face - Access can only read about 20-30 %
of error messages from the sql server. I have had debates with other
Access people inhouse at my place who insisted that I was wrong. To
date - no one has been able to show otherwise. .Net on the otherhand,
is the new generation of technology which includes sql server
interactivity and has specifically addressed these issues successfully
- 100%.

Bottom line - Access is a great tool - but against a sql server backend
- must proceed with great caution. It is all doable - just a lot of
advanced functionality is not available in Access for dealing with sql
servers. So I guess my method does not really present any advantages
over editing/updating data directly from the table. Except if migration
to .Net is in the works - this method will at least get you started
using the .Net paradigm.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
--
Posted Via Newsfeeds.com Premium Usenet Newsgroup Service
----------------------------------------------------------
http://www.Newsfeeds.com

Sep 17 '08 #9

P: n/a
I give up.

Rich P <rp*****@aol.comwrote in news:k8*****************@newsfe04.iad:
In theory, if referential integrity is set and keys are set then if
user1 edits/updates data this user will get an error message from the
sql server if the business rules are not met - usually some key
violation error. Lets say user1 edits/updates data and then user2
edits/updates the same data 1 millisecond after user1 using the method
I
suggested. If user2 is following the company's business rules and
enters the same data then this user will likely get some key violation
message of duplicate data - the record already exists. But if user2 is
actually changing the data that User1 just edited - meaning user2 came
along after user1 - say an hour later - it doesn't matter if my method
is used or if User2 edits the data directly in the table.

The problem with Access here is that Access just can't read most of the
error messages from the sql server. It just can't. Slice it/dice it -
argue till you are blue in the face - Access can only read about 20-30
%
of error messages from the sql server. I have had debates with other
Access people inhouse at my place who insisted that I was wrong. To
date - no one has been able to show otherwise. .Net on the otherhand,
is the new generation of technology which includes sql server
interactivity and has specifically addressed these issues successfully
- 100%.

Bottom line - Access is a great tool - but against a sql server backend
- must proceed with great caution. It is all doable - just a lot of
advanced functionality is not available in Access for dealing with sql
servers. So I guess my method does not really present any advantages
over editing/updating data directly from the table. Except if
migration
to .Net is in the works - this method will at least get you started
using the .Net paradigm.

Rich

*** Sent via Developersdex http://www.developersdex.com ***



--
-
lyle fairfield
Sep 17 '08 #10

P: n/a
On Sep 17, 12:06*pm, lyle fairfield <lylef...@yah00.cawrote:
I give up.

Rich P <rpng...@aol.comwrote innews:k8*****************@newsfe04.iad:


In theory, if referential integrity is set and keys are set then if
user1 edits/updates data this user will get an error message from the
sql server if the business rules are not met - usually some key
violation error. *Lets say user1 edits/updates data and then user2
edits/updates the same data 1 millisecond after user1 using the method
I
suggested. *If user2 is following the company's business rules and
enters the same data then this user will likely get some key violation
message of duplicate data - the record already exists. *But if user2 is
actually changing the data that User1 just edited - meaning user2 came
along after user1 - say an hour later - it doesn't matter if my method
is used or if User2 edits the data directly in the table.
The problem with Access here is that Access just can't read most of the
error messages from the sql server. *It just can't. *Slice it/dice it -
argue till you are blue in the face - Access can only read about 20-30
%
of error messages from the sql server. *I have had debates with other
Access people inhouse at my place who insisted that I was wrong. *To
date - no one has been able to show otherwise. *.Net on the otherhand,
is the new generation of technology which includes sql server
interactivity and has specifically addressed these issues successfully
- 100%. *
Bottom line - Access is a great tool - but against a sql server backend
- must proceed with great caution. *It is all doable - just a lot of
advanced functionality is not available in Access for dealing with sql
servers. *So I guess my method does not really present any advantages
over editing/updating data directly from the table. *Except if
migration
to .Net is in the works - this method will at least get you started
using the .Net paradigm.
Rich
*** Sent via Developersdexhttp://www.developersdex.com***

--
-
lyle fairfield- Hide quoted text -

- Show quoted text -
So my original thought of setting a flag to a value on a record when
user-1 selects that record is not a good idea? I don't see how to set
the isolation level using VBA and it appears that it's used for
transaction processing only.
Sep 17 '08 #11

P: n/a
rkc
On Sep 17, 1:06*pm, lyle fairfield <lylef...@yah00.cawrote:
I give up.
20 to 30% give up or 100% give up?
Sep 17 '08 #12

P: n/a
rkc <rk*@rkcny.comwrote in news:d5b80436-5d99-4e05-a702-
86**********@c65g2000hsa.googlegroups.com:
On Sep 17, 1:06*pm, lyle fairfield <lylef...@yah00.cawrote:
>I give up.

20 to 30% give up or 100% give up?
I could never 100% give up on anyone.
My [Give-Up to Rich] ratio joins my [Give-Up to a Bucket of Rocks] ratio at
99.44%.

--
-
lyle fairfield
Sep 18 '08 #13

P: n/a
rkc
On Sep 18, 7:08*am, lyle fairfield <lylef...@yah00.cawrote:
rkc <r...@rkcny.comwrote in news:d5b80436-5d99-4e05-a702-
86321a915...@c65g2000hsa.googlegroups.com:
On Sep 17, 1:06*pm, lyle fairfield <lylef...@yah00.cawrote:
I give up.
20 to 30% give up or 100% give up?

I could never 100% give up on anyone.
My [Give-Up to Rich] ratio joins my [Give-Up to a Bucket of Rocks] ratio at
99.44%.
Hope is alive.
Sep 18 '08 #14

P: n/a
"Chris O'C via AccessMonster.com" <u29189@uwewrote in
news:8a4a590139c8b@uwe:
I don't think you'll see any effect on the SQL Server tables when
"edited record" is selected, because it's a setting that applies to
Jet tables if the db was opened with row level locking set.
Are you suggesting that

MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to No Locks

and

MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to Edited Record

behave in the same way when updating data that has been changed subsequent
to loading the data into the form?

This is not the case.

---------------------------
No Locks lets me change the data regardless of a whether or not it has been
changed extraneously.
---------------------------

Edited Record gives me this warning message when the data has been changed
extraneously:

---------------------------
---------------------------
Microsoft Office Access
---------------------------
The data has been changed.
Another user edited this record and saved the changes before you attempted
to save you changes.
Re-edit the record.
---------------------------
OK
---------------------------
---------------------------

Reversing the process, that is trying to change the record in Microsoft SQL
Server Management Studio after having made a change in Access-ODBC gives:

---------------------------
---------------------------
Microsoft SQL Server Management Studio
---------------------------
Data has changed since the Results pane was last retrieved. Do you want to
save your changes now?
(Optimistic Concurrency Control Error)

Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.
---------------------------
Yes No Cancel Help
---------------------------
---------------------------

As an aside, I prefer the ADP handling of the situation which gives:

---------------------------
---------------------------
Write Conflict
---------------------------
This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make changes.
---------------------------
Save Record Copy to Clipboard Drop Changes
---------------------------
---------------------------

--
lyle fairfield
Sep 18 '08 #15

P: n/a
No, when the op posted the question, he/she was using Access 2003, so I said
"use locks on edited record" and he/she said "I'm really using SQL Server 7"
and I said "I don't think a setting for Jet is going to do jack on SQL Server
tables".

Chris
Microsoft MVP
lyle fairfield wrote:
>I don't think you'll see any effect on the SQL Server tables when
"edited record" is selected, because it's a setting that applies to
Jet tables if the db was opened with row level locking set.

Are you suggesting that

MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to No Locks

and

MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to Edited Record

behave in the same way when updating data that has been changed subsequent
to loading the data into the form?

This is not the case.
--
Message posted via http://www.accessmonster.com

Sep 18 '08 #16

P: n/a
On Sep 18, 6:51*am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:
No, when the op posted the question, he/she was using Access 2003, so I said
"use locks on edited record" and he/she said "I'm really using SQL Server7"
and I said "I don't think a setting for Jet is going to do jack on SQL Server
tables".

Chris
Microsoft MVP

lyle fairfield wrote:
I don't think you'll see any effect on the SQL Server tables when
"edited record" is selected, because it's a setting that applies to
Jet tables if the db was opened with row level locking set.
Are you suggesting that
MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to No Locks
and
MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to Edited Record
behave in the same way when updating data that has been changed subsequent
to loading the data into the form?
This is not the case.

--
Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -
I want user-2 notified that user-1 is in the record as soon as user-2
requests the record. The only way I see to do this is with a flag on
the record. I don't want to handle the write-conflict error at all.
Sep 18 '08 #17

P: n/a

<em******@kumc.eduwrote in message
news:5d**********************************@a1g2000h sb.googlegroups.com...
On Sep 18, 6:51 am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:
No, when the op posted the question, he/she was using Access 2003, so I
said
"use locks on edited record" and he/she said "I'm really using SQL Server
7"
and I said "I don't think a setting for Jet is going to do jack on SQL
Server
tables".

Chris
Microsoft MVP

lyle fairfield wrote:
I don't think you'll see any effect on the SQL Server tables when
"edited record" is selected, because it's a setting that applies to
Jet tables if the db was opened with row level locking set.
Are you suggesting that
MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to No Locks
and
MS-Access (2007)
ODBC
MS-SQL Server
Bound Form with Record Locks set to Edited Record
behave in the same way when updating data that has been changed
subsequent
to loading the data into the form?
This is not the case.

--
Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -
>I want user-2 notified that user-1 is in the record as soon as user-2
requests the record. The only way I see to do this is with a flag on
the record. I don't want to handle the write-conflict error at all.
Have your form attempt to place a write lock on the record when opened. If
the lock fails, user-1 has it opened, display a message to User-2 and return
to the previous record. When User-1 exits the record, have the form release
the lock.
Sep 18 '08 #18

P: n/a
On Sep 18, 4:28*pm, "paii, Ron" <n...@no.comwrote:
<emann...@kumc.eduwrote in message

news:5d**********************************@a1g2000h sb.googlegroups.com...
On Sep 18, 6:51 am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:


No, when the op posted the question, he/she was using Access 2003, so I
said
"use locks on edited record" and he/she said "I'm really using SQL Server
7"
and I said "I don't think a setting for Jet is going to do jack on SQL
Server
tables".
Chris
Microsoft MVP
lyle fairfield wrote:
>I don't think you'll see any effect on the SQL Server tables when
>"edited record" is selected, because it's a setting that applies to
>Jet tables if the db was opened with row level locking set.
>Are you suggesting that
>MS-Access (2007)
>ODBC
>MS-SQL Server
>Bound Form with Record Locks set to No Locks
>and
>MS-Access (2007)
>ODBC
>MS-SQL Server
>Bound Form with Record Locks set to Edited Record
>behave in the same way when updating data that has been changed
subsequent
>to loading the data into the form?
>This is not the case.
--
Message posted viahttp://www.accessmonster.com-Hide quoted text -
- Show quoted text -
I want user-2 notified that user-1 is in the record as soon as user-2
requests the record. *The only way I see to do this is with a flag on
the record. *I don't want to handle the write-conflict error at all.

Have your form attempt to place a write lock on the record when opened. If
the lock fails, user-1 has it opened, display a message to User-2 and return
to the previous record. When User-1 exits the record, have the form release
the lock.- Hide quoted text -

- Show quoted text -
How do I set a write lock on a record? I'm not seeing anything in
newgroups or my Access manual about how to set a write lock. There is
a Record Locks property on the form which is set to Edited Record.
But according to Chris (above), this doesn't apply to linked SQL
tables.
Sep 19 '08 #19

P: n/a

<em******@kumc.eduwrote in message
news:d3**********************************@t54g2000 hsg.googlegroups.com...
On Sep 18, 4:28 pm, "paii, Ron" <n...@no.comwrote:
<emann...@kumc.eduwrote in message

news:5d**********************************@a1g2000h sb.googlegroups.com...
On Sep 18, 6:51 am, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:


No, when the op posted the question, he/she was using Access 2003, so I
said
"use locks on edited record" and he/she said "I'm really using SQL
Server
7"
and I said "I don't think a setting for Jet is going to do jack on SQL
Server
tables".
Chris
Microsoft MVP
lyle fairfield wrote:
>I don't think you'll see any effect on the SQL Server tables when
>"edited record" is selected, because it's a setting that applies to
>Jet tables if the db was opened with row level locking set.
>Are you suggesting that
>MS-Access (2007)
>ODBC
>MS-SQL Server
>Bound Form with Record Locks set to No Locks
>and
>MS-Access (2007)
>ODBC
>MS-SQL Server
>Bound Form with Record Locks set to Edited Record
>behave in the same way when updating data that has been changed
subsequent
>to loading the data into the form?
>This is not the case.
--
Message posted viahttp://www.accessmonster.com-Hide quoted text -
- Show quoted text -
I want user-2 notified that user-1 is in the record as soon as user-2
requests the record. The only way I see to do this is with a flag on
the record. I don't want to handle the write-conflict error at all.

Have your form attempt to place a write lock on the record when opened. If
the lock fails, user-1 has it opened, display a message to User-2 and
return
to the previous record. When User-1 exits the record, have the form
release
the lock.- Hide quoted text -

- Show quoted text -
>How do I set a write lock on a record? I'm not seeing anything in
newgroups or my Access manual about how to set a write lock. There is
a Record Locks property on the form which is set to Edited Record.
But according to Chris (above), this doesn't apply to linked SQL
tables.

I have never done it, but look at DAO or ADO record sets. You may need to
use an unbound form.
Sep 19 '08 #20

This discussion thread is closed

Replies have been disabled for this discussion.