Warn user-2 if user-1 on requested record

[emanning]

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.

[Chris O'C via AccessMonster.com]

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.e du wrote:

>Is this a good way to do this?

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200809/1

[emanning]

On Sep 16, 11:17*am, "Chris O'C via AccessMonster.c om" <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.e du wrote:

Is this a good way to do this?

--
Message posted via AccessMonster.c omhttp://www.accessmonst er.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.

[Chris O'C via AccessMonster.com]

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
adLockPessimist ic 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.e du wrote:

>On Sep 16, 11:17Â*am, "Chris O'C via AccessMonster.c om" <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.....i t'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

[Rich P]

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 ***

[lyle fairfield]

Rich P <rp*****@aol.co mwrote in news:12******** *****@news.news feeds.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

[emanning]

On Sep 16, 4:15*pm, lyle fairfield <lylef...@yah00 .cawrote:

Rich P <[e-mail] mwrote innews:12****** *******@news.ne wsfeeds.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.

[Rich P]

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

[Rich P]

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

[lyle fairfield]

I give up.

Rich P <rp*****@aol.co mwrote in news:k8******** *********@newsf e04.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