How can you prevent the data changes message during editing a

Hi David,
I think you are right that this is not a server's job. I assumed that the
server has the original record available in the Deleted version of the recor
d
in a trigger - but this is probably not the record state the user first read
into a form.
My problem is that I have a big application (a mail-order processing system)
written in Access 2000 FE (.mdb) file with lots of forms with bound ODBC
server tables/queries. I have 850 queries and a lot of code. Up to now I use
d
pessimistic locking but this is not possible anymore (I think) because if I
link via ODBC to SQL server I do not have the option to lock pessimisticly
anymore.
In principle I could use the optimistic locking but how can I deal with data
changes in a record made by other users/jobs. If I cannot lock, a job or
program could "get caught out" as well and how would the program then know
what to do (from the choices: copy the data to the clipboard, overwrite or
loose it). I really think Microsoft should solve this problem by changing th
e
bound forms so that only the changed fields get updated and overwrite by
default - may with an option to switch this behaviour on and off...
It should never get to that stage, it must be prevented via short time locks
that a record gets changed during a read and update transaction.
It is okay to assume that if a user field change clashes with another user
field change the last writing user "wins" provided only the changed fields
will be updated.
If I could I would like to leave everything in ODBC or DAO and convert
slowly to stored procedures and use ADO, ... otherwise it would be a mega jo
b
to convert all in one go. I might have to do this if there is no way around
the locking problem.
Do you or anybody else know a way around it - it would be like light in the
dark tunnel for me!
Thanks.
Oliver
"David Gugick" wrote:

> Oliver wrote:
> I don't agree with your assessment. This is an application programming
> issue and not one for the database to manage on its own. You're
> suggesting that the server update a row that has been updated in the
> interim by another process just because the columns being updated are
> not the same. I would argue that there's no way for SQL Server to know
> if changing a single column value in a row would somehow affect business
> rules and know whether or not the proposed row changes are valid.
> Many applications can deal with this scenario by assuming the last
> update should be the most current. In that scenario, there is no
> additional programming required. If you need to manage concurrency and
> changes to a row by another session to avoid overwriting those changes,
> you should use a timestamp. You pass the timestamp value with the other
> column values to your stored procedure and the procedure does the work.
> The application should be able to handle the condition where the row was
> not updated because an error was raised and then requery the data and
> inform the end-user. All your DML should be done in stored procedures.
> In the case where there is no end-user, your application code should log
> an error condition that can be managed manually in an interactive
> fashion or it could requery the data, assuming this is something it can
> work around.
> For a nightly batch process, you could lock the entire table within your
> transaction.
>
> I'm not sure what you are describing here. A change is a change.
> Presumably, you have a stored procedure to manage each type of change to
> your data. Maybe you can elaborate on this part a little more.
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>Oliver wrote:
> Hi David,
> I think you are right that this is not a server's job. I assumed that
> the server has the original record available in the Deleted version
> of the record in a trigger - but this is probably not the record
> state the user first read into a form.
> Oliver
Can't you use either the dbOptimistic or dbOptimisticValue LockEdit
parameter on the Recordset? I think dbOptimisticValue does a column by
column value comparison before updating. It's been a long time since
I've looked at DAO, so this might be a better question for an Access
newsgroup.

> I really think Microsoft
> should solve this problem by changing the bound forms so that only
> the changed fields get updated and overwrite by default - may with an
> option to switch this behaviour on and off...
I think this is a bit of a risk for those users programming using bound
forms (not recommended in my opinion for any important application - but
that's another topic). The point I tried to make in my last post was
that it's too much of a risk to update some columns and not others -
there's no way to know if the relationship between them is important in
some manner.
David Gugick - SQL Server MVP
Quest Software