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.

[emanning]

On Sep 17, 12:06*pm, lyle fairfield <lylef...@yah00 .cawrote:

I give up.

Rich P <[e-mail] mwrote innews:k8****** ***********@new sfe04.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 Developersdexht tp://www.developersd ex.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.

[rkc]

On Sep 17, 1:06*pm, lyle fairfield <lylef...@yah00 .cawrote:

I give up.

20 to 30% give up or 100% give up?

[lyle fairfield]

rkc <rk*@rkcny.comw rote in news:d5b80436-5d99-4e05-a702-
86**********@c6 5g2000hsa.googl egroups.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

[rkc]

On Sep 18, 7:08*am, lyle fairfield <lylef...@yah00 .cawrote:

rkc <[e-mail] wrote in news:d5b80436-5d99-4e05-a702-
86321a915...@c6 5g2000hsa.googl egroups.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.

[lyle fairfield]

"Chris O'C via AccessMonster.c om" <u29189@uwewrot e in
news:8a4a590139 c8b@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

[Chris O'C via AccessMonster.com]

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

[emanning]

On Sep 18, 6:51*am, "Chris O'C via AccessMonster.c om" <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.accessmonst er.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.

[paii, Ron]

<em******@kumc. eduwrote in message
news:5d******** *************** ***********@a1g 2000hsb.googleg roups.com...
On Sep 18, 6:51 am, "Chris O'C via AccessMonster.c om" <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.accessmonst er.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.

[emanning]

On Sep 18, 4:28*pm, "paii, Ron" <[e-mail] te:

<emann...@kumc. eduwrote in message

news:5d******** *************** ***********@a1g 2000hsb.googleg roups.com...
On Sep 18, 6:51 am, "Chris O'C via AccessMonster.c om" <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.accessmonst er.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.

[paii, Ron]

<em******@kumc. eduwrote in message
news:d3******** *************** ***********@t54 g2000hsg.google groups.com...
On Sep 18, 4:28 pm, "paii, Ron" <[e-mail] te:

<emann...@kumc. eduwrote in message

news:5d******** *************** ***********@a1g 2000hsb.googleg roups.com...
On Sep 18, 6:51 am, "Chris O'C via AccessMonster.c om" <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.accessmonst er.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.