Another record locking issue...

Hi all,

I'm working on a Planning Databse for a factory that allows jobs to be allocated to machines and to be given a priority. This info. is displayed on an Access 2000 form e.g.

MACHINE ONE MACHINE TWO
Priority Job Number Priority Job Number
1 J111 1 J654
2 J562 2 J865
3 j231 3 J821

Both the Production Manager and the Factory Manager have this form open at all times. The problem is this, if for example the Factory Manager changes the priority of a job on his PC, the change is not shown on the Production Managers PC (cos' his screen has not been refreshed with update) The Production Manager therefore has old data on his screen and could mistakenly think that the job priority had not changed at all...
I have managed to write some VBA code to get round this but I was wondering if there was any way of 'pushing ' changes to the front-end when they are made or of using record-locking etc. to achieve the same affect...

Thanks
MThat situation shouldn't happen. However, review optimistic and pessimistic locking. Pessimisitic should definitely work if you're having problems with optimistic.|||Give them a refresh button?|||I have already implemented a refresh button but every now and again the users forget to press it and this causes problems. Also tried using the Access Timer Interval to refersh every n seconds but that caused other problems that I won't go into here.

Sorry I didn't reply sooner as I was off ill yesterday :-(|||The only way that I can see that would resolve your problem is implementing some kind of refresh interval on the front end, as the refresh button aproach apparently didn't work!

The problem with "forcing new entries" is basically that you actually retrieve data from the db, and thus it's kind of difficult to force a re-fetch from the db side...|||Listen, I can send you some monkeys that can click the refresh button for you...|||Hey guys,

I've just realised that i never mentioned the back-end is a SQL Server 2000 database!!! Basically, I just don't understand record locking well enough and will have to go and do some research...thanks for all your inpute.

Another locking question

From this article by
Kalen...http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26922...
she mentioned
"For INSERTS which are blocking, you might want to make sure that your
clustered index is NOT on a column that determines the order that the new
rows are arriving. For example, don't put your clustered index on OrderDate
or OrderNumber, which will always be increasing"
My quesion is why not ?I believe Kalen is just saying that if your inserts are causing blocking you
would not be helping the situation by having an index that uses these same
inserts as the logic to always through the data to the bottom of the last
leaf level of the table (clustered). I think that for inserts that are not
causing blocking it is quite acceptable to use a clustered index on a
sequential key (like an identity column).
Later in that same article Kalen talks about key locks, I've fixed quite a
few blocking issues by adding a clustered index to a table which then would
acquire these key locks. So you would have to test to ensure your inserts
are causing blocking.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OivRkZZhDHA.484@.TK2MSFTNGP12.phx.gbl...
> From this article by
> Kalen...http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26922...
> she mentioned
> "For INSERTS which are blocking, you might want to make sure that your
> clustered index is NOT on a column that determines the order that the new
> rows are arriving. For example, don't put your clustered index on
OrderDate
> or OrderNumber, which will always be increasing"
> My quesion is why not ?
>