Re: Record Locking with SQL Server tables and Access db Client

The reason you don't get the same kind of locking with SQL Server as
you did with Access is because you would kill performance on the
server every time a user edited a row - SQLS won't even let another
proceess *read* a row if it is locked for an update, so users
attempting SELECT * FROM tablename would be totally blocked.
Access/Jet was built to run as a single-user app on the local machine,
not as a server, which is why you have pessimistic locking features
built into its forms package.

You can use flags to indicate a record is locked, or implement a
customized concurrency scheme in Access, loading data read-only. Users
select a record to edit, and you load only that single record,
providing Save and Cancel buttons. You need to handle concurrency
conflicts and manage form state in your code, but it is the most
scalable way of dealing with the problem.

-Mary

On Mon, 02 Jul 2007 12:02:46 -0700, anneo <aorourke3@xxxxxxx> wrote:

I have a situation where I have linked tables from SQL Server in
Access 2000 using ODBC.
I am using these tables via a complex query as the record source for a
form.

When a user starts to edit a record via the form, we need the record
locked, so that if another user (on another PC) attempts to edit it,
they are informed that the record is already being edited, so they
cannot make changes.

With SQL Server tables there is only a warning when the second person
finishes editing to let them know that they will be overwriting
someone else's changes.

The form properties are set to record locking, but this does not seem
to work on the linked SQL Server table/query. It worked when the
tables were Access based.

Is there some setting either in Access or in SQL Server or in the Link
setup that will enable the record locking? Rewriting the queries to
eliminate the ODBC usage at this time is not feasible.

I am considering adding data elements to the table being modified that
flags that it is locked and checking it "on dirty" to notify the other
users (basically implementing locking), but it certainly seems that
Microsoft would not have taken out this basic critical functionality.

If anyone knows of a setting that can activate this please let me
know. I've seen several posts asking this question, but no solutions.
.

Quantcast