|
Home > Archive > MS Access Multiuser > February 2006 > Locking scheme for ODBC linked tables? Access + SQL Server
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Locking scheme for ODBC linked tables? Access + SQL Server
|
|
|
| Sorry for posting in three newsgroups, but all seem appropriate and the
topic seems to have sparse coverage in general.
Using Access 2003 linked tables to SQL Server 2000 via ODBC. What's the
simplest way to implement explicit control over record locking? Even
with a timestamp in the table, letting Access' built in form record
locking warnings seem a bit tardy.
Probably my ideal scenario is something like: A user finds a record in a
form, which is not editable by default. They click on a button and edits
are enabled, unless someone beat them to it. If someone is already
editing the record a notification would appear. I don't think that this
would be a major programming feat, but it would be interesting to see
what others have come up with.
| |
| Sylvain Lafontaine 2006-02-01, 8:29 pm |
| Well, this newsgroup is about ADP project and not ODBC linked tables, so
it's not appropriate.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"kiln" <kiln@brick-like.com> wrote in message
news:MPG. 1e4a9a8511264b39899a
c@msnews.microsoft.com...
> Sorry for posting in three newsgroups, but all seem appropriate and the
> topic seems to have sparse coverage in general.
>
> Using Access 2003 linked tables to SQL Server 2000 via ODBC. What's the
> simplest way to implement explicit control over record locking? Even
> with a timestamp in the table, letting Access' built in form record
> locking warnings seem a bit tardy.
>
> Probably my ideal scenario is something like: A user finds a record in a
> form, which is not editable by default. They click on a button and edits
> are enabled, unless someone beat them to it. If someone is already
> editing the record a notification would appear. I don't think that this
> would be a major programming feat, but it would be interesting to see
> what others have come up with.
| |
|
| Hi Sylvain :~)
You will find that if you edit the "Newsgroups" list in your reply
to field, you can limit your responses to only the groups in which
you wish the reply to appear.
Kiln:
The traditional/original Access method is to use a Separate
Form for edit/update. One form for finding and selecting
the record (read only, no locks), another form for edit/update
of the specific record. (pessimistic locking).
It works well, and is really easy to implement.
(david)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:ONGjZ02JGHA.3272@tk2msftngp13.phx.gbl...
> Well, this newsgroup is about ADP project and not ODBC linked tables, so
> it's not appropriate.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "kiln" <kiln@brick-like.com> wrote in message
> news:MPG. 1e4a9a8511264b39899a
c@msnews.microsoft.com...
>
>
| |
|
| I posted to the adp list because I figured the level of experise with
sql server and access, including odbc, was high there. Not a perfectly
appropriate ng by name, but probably the right people.
Sylvian, I've read around a good bit and there are many comments from
folks like Mary Chipman to the effect that pessimistic locking can
create a massive performance hit and should be avoided. What has been
your experience? Also, how do you implement pessimistic with odbc?
According to help and what I've read, the locks setting on Access forms
is ignored if using an odbc data source. It's always "no locks". I've
spent a couple hours reading on this subject and haven't come up with a
lot of specifics that sound like they'd work, mostly things that don't
work. I've also experimented, using two forms in diff dbs that pull odbc
data from the same db and record, and the behavior I see seems to be
like the regular jet optimistic locking.
Just using a separate form for updating wouldn't seem to provide any
inherant solution. But however you've come to a solution, if you could
provide a bit of detail, that would be useful. Thanks
In article <uUFYB23JGHA.3224@TK2MSFTNGP09.phx.gbl>,
david@epsomdotcomdot
au says...
> Hi Sylvain :~)
>
> You will find that if you edit the "Newsgroups" list in your reply
> to field, you can limit your responses to only the groups in which
> you wish the reply to appear.
>
> Kiln:
>
> The traditional/original Access method is to use a Separate
> Form for edit/update. One form for finding and selecting
> the record (read only, no locks), another form for edit/update
> of the specific record. (pessimistic locking).
>
> It works well, and is really easy to implement.
>
> (david)
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:ONGjZ02JGHA.3272@tk2msftngp13.phx.gbl...
>
>
>
| |
| Sylvain Lafontaine 2006-02-02, 3:25 am |
| The topic of optimistic and pessimistic locking could easily be the subject
of a whole book, so I won't try to cover this topic here. However, here a
few points:
1- Like you said, Access doesn't use pessimistic locking (if I remember
correctly) when linking to a SQL-Server; so you have to use unbound forms if
you want to use pessimistic locking. This is true for both ODBC linked
tables and ADP projects.
I think that the commentary of David about using a second form for
pessimistic locking is for when you are using JET as the backend and not
SQL-Server. (But as always, I might be wrong.)
2- Like Mary C. has said, using pessimistic locking can create some massive
performance hit so instead of using it, you should replace it with some
another scheme; for example the one that you have mentionned. You should
also take into consideration the possibility that someone may *forget* to
release the lock after a certain amount of time and provide a mecanism to
override it.
3- Finally, you should ask yourself if you really need to use pessimistic
locking. Myself, I can't remember the last time I used it in a project and
I'm perfectly happy with the optimistic locking of Access since many years.
For example, in a sport software that I'm now in the process of creating, I
don't see why two people should (try to) update the statistics for the same
team and the same game at the same time. Doing so will probably be the
result of some other error by these two people.
There is the very light possibility that someone may try to change some
detail for a player (for example his permanent code) while another will try
to change some other detail (his telephone number?) at the same time;
however, if this happens one time in the next 10 years, all they will have
to do will be for one of these two persons to reenter the value a second
time. Not a big deal in my opinion. The only thing important important to
me is that they got an error message from Access.
In some occasions, pessimistic locking is required; however and too
often, it's only the search of *perfection* by the programmer without any
real need behind it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"kiln" <kiln@brick-like.com> wrote in message
news:MPG. 1e4b43629ffa08599899
ad@msnews.microsoft.com...[color=darkred]
>I posted to the adp list because I figured the level of experise with
> sql server and access, including odbc, was high there. Not a perfectly
> appropriate ng by name, but probably the right people.
>
> Sylvian, I've read around a good bit and there are many comments from
> folks like Mary Chipman to the effect that pessimistic locking can
> create a massive performance hit and should be avoided. What has been
> your experience? Also, how do you implement pessimistic with odbc?
> According to help and what I've read, the locks setting on Access forms
> is ignored if using an odbc data source. It's always "no locks". I've
> spent a couple hours reading on this subject and haven't come up with a
> lot of specifics that sound like they'd work, mostly things that don't
> work. I've also experimented, using two forms in diff dbs that pull odbc
> data from the same db and record, and the behavior I see seems to be
> like the regular jet optimistic locking.
>
> Just using a separate form for updating wouldn't seem to provide any
> inherant solution. But however you've come to a solution, if you could
> provide a bit of detail, that would be useful. Thanks
>
> In article <uUFYB23JGHA.3224@TK2MSFTNGP09.phx.gbl>,
> david@epsomdotcomdot
au says...
| |
|
| In this case, the client wants user B to be told the rec is already
being edited by user A before user B starts working on the record. They
have opt locking in place and have had too many collisions, want an
alternate route.
I can code something for this, but was rather hoping that someone who
has done it before could share some specfics. I think it could be a bit
tricky to implement. It'd be better to deploy a scheme that is mature.
In article <uNukpo8JGHA.1028@TK2MSFTNGP11.phx.gbl>, "Sylvain
Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> says...
> The topic of optimistic and pessimistic locking could easily be the subject
> of a whole book, so I won't try to cover this topic here. However, here a
> few points:
>
> 1- Like you said, Access doesn't use pessimistic locking (if I remember
> correctly) when linking to a SQL-Server; so you have to use unbound forms if
> you want to use pessimistic locking. This is true for both ODBC linked
> tables and ADP projects.
>
> I think that the commentary of David about using a second form for
> pessimistic locking is for when you are using JET as the backend and not
> SQL-Server. (But as always, I might be wrong.)
>
> 2- Like Mary C. has said, using pessimistic locking can create some massive
> performance hit so instead of using it, you should replace it with some
> another scheme; for example the one that you have mentionned. You should
> also take into consideration the possibility that someone may *forget* to
> release the lock after a certain amount of time and provide a mecanism to
> override it.
>
> 3- Finally, you should ask yourself if you really need to use pessimistic
> locking. Myself, I can't remember the last time I used it in a project and
> I'm perfectly happy with the optimistic locking of Access since many years.
> For example, in a sport software that I'm now in the process of creating, I
> don't see why two people should (try to) update the statistics for the same
> team and the same game at the same time. Doing so will probably be the
> result of some other error by these two people.
>
> There is the very light possibility that someone may try to change some
> detail for a player (for example his permanent code) while another will try
> to change some other detail (his telephone number?) at the same time;
> however, if this happens one time in the next 10 years, all they will have
> to do will be for one of these two persons to reenter the value a second
> time. Not a big deal in my opinion. The only thing important important to
> me is that they got an error message from Access.
>
> In some occasions, pessimistic locking is required; however and too
> often, it's only the search of *perfection* by the programmer without any
> real need behind it.
>
>
| |
| david epsom dot com dot au 2006-02-02, 8:25 pm |
| Before opening the Edit form, check the user ID on the record.
When you open the Edit form, you write the user ID to the record.
When you close the edit form, you clear the user ID from the record.
You need to have a separate 'clear locks' function in case
you loose a connection.
If you have a very large number of records, you do it by writing
the locks to a separate table (so you don't waste the space of
the lock field on every record.)
If you have incoming records, you do it by moving the incoming
record out of the incoming table and into the edit table when
selected by a user.
Actually locking the record (which can be done using a transaction)
is a bit tricky, and you still have to do special tests to check
the lock status because by default Access will want to 'try again
later' if the record is locked.
(david)
"kiln" <kiln@brick-like.com> wrote in message
news:MPG. 1e4bdba119555b039899
ae@msnews.microsoft.com...[color=darkred]
> In this case, the client wants user B to be told the rec is already
> being edited by user A before user B starts working on the record. They
> have opt locking in place and have had too many collisions, want an
> alternate route.
>
> I can code something for this, but was rather hoping that someone who
> has done it before could share some specfics. I think it could be a bit
> tricky to implement. It'd be better to deploy a scheme that is mature.
>
> In article <uNukpo8JGHA.1028@TK2MSFTNGP11.phx.gbl>, "Sylvain
> Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> says...
| |
|
| Thanks for that input. I'd like to hear other approaches and any gory
detail that might help with the actual implementations with any approach
(ie what are the issues and solutions re david's last sentance)
In article <OuyIlHFKGHA.516@TK2MSFTNGP15.phx.gbl>,
david@epsomdotcomdot
au says...[color=darkred]
> Before opening the Edit form, check the user ID on the record.
>
> When you open the Edit form, you write the user ID to the record.
>
> When you close the edit form, you clear the user ID from the record.
>
> You need to have a separate 'clear locks' function in case
> you loose a connection.
>
> If you have a very large number of records, you do it by writing
> the locks to a separate table (so you don't waste the space of
> the lock field on every record.)
>
> If you have incoming records, you do it by moving the incoming
> record out of the incoming table and into the edit table when
> selected by a user.
>
> Actually locking the record (which can be done using a transaction)
> is a bit tricky, and you still have to do special tests to check
> the lock status because by default Access will want to 'try again
> later' if the record is locked.
>
> (david)
>
> "kiln" <kiln@brick-like.com> wrote in message
> news:MPG. 1e4bdba119555b039899
ae@msnews.microsoft.com...
|
|
|
|
|