Behavior in multiuser envir

[David Portwood]

I have a split multiuser app deployed (a document tracking system) which has
about 50 users total with 4 or 5 data entry people entering data all day
into a main table. Probably 20 to 30 people have the app running throughout
the day on their desktop. The app works very well and my users are very
happy with it, particularly management, but there is an awkwardness during
data entry which I think relates to multiuser behavior that I hope to learn
more about from the gurus here.

Occasionally, when the de person tries to move off a new record, the system
seems to freeze for a minute or so. Sometimes the system recovers and
continues, and sometimes the de person receives the message "Could not
update; currently locked." I assume this means someone else is trying to run
a report and Access has locked the table while the report is collecting
data. Access has a default retry period and if the other user completes the
reports's query before the retry period times out, the system recovers and
continues, otherwise it gives up and displays the error message. Can anyone
confirm this and maybe elaborate?

What happens when a user clicks the "Report" button and runs a report
against the main table while the data entry people are entering data? If a
de person enters a record before the query on which the report is based
completes, does the query restart:? When the query on which the report is
based begins to run, does it lock out the de people from entering new
records? I have the Access "No Locks" option set.

Assuming the above is true, I have doubled the default Update Retry Interval
to 500 ms and also doubled the default Number Of Retries to 4. I could
increase both to the max, of course. However, while the system retries it
would still appear to be frozen to the de person, so I don't think this
would solve the problem.

My background is Paradox for Windows. In this app, there are query restart
options - Restart/Don't Restart on source table changes, lock/don't lock
source tables, etc. - which I can select as appropriate for a multiuser
environment. I don't see the same options in Access except the overall "No
Locks" property.

Am I overlooking something? Could general network "busy-ness" be causing the
problems? There are periods during the day when the network becomes very
busy and all local functions slow down significantly.

Did any of you run into the same kind of problem and if so how did you
handle it? How should it be handled, theoretically?

 

[Tom van Stiphout]

On Sun, 16 Sep 2007 08:02:07 -0700, "David Portwood"

Check out this article:
http://support.microsoft.com/kb/300216
There are others as well. Search for Jet+Record+Locking.

-Tom.

 

[David Portwood]

Thanks, Tom. I found a lot of information. Unfortunately, much of it was
over my head. Not an unusual situation for me...

 

[Tony Toews [MVP]]

Sometimes the system recovers and
continues, and sometimes the de person receives the message "Could not
update; currently locked." I assume this means someone else is trying to run
a report and Access has locked the table while the report is collecting
data.

No. Reports do not do locks on the records as they are read only.

The problem might be more along the lines of two users trying to
update the same page which might contain multiple records at the same
time.

What happens when a user clicks the "Report" button and runs a report
against the main table while the data entry people are entering data? If a
de person enters a record before the query on which the report is based
completes, does the query restart:?

Not as far as I know and I highly doubt it.

When the query on which the report is
based begins to run, does it lock out the de people from entering new
records?
No.

Am I overlooking something? Could general network "busy-ness" be causing the
problems? There are periods during the day when the network becomes very
busy and all local functions slow down significantly.

Ah, that is quite possible. Access is the proverbial canary in a coal
mine. It needs a good, stable network as it does send a lot of
traffic at times and expects a timely response.

As far as your problem goes

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Fabiola]

Alguien habla castellano?

No. Reports do not do locks on the records as they are read only.

The problem might be more along the lines of two users trying to
update the same page which might contain multiple records at the same
time.


Not as far as I know and I highly doubt it.


Ah, that is quite possible. Access is the proverbial canary in a coal
mine. It needs a good, stable network as it does send a lot of
traffic at times and expects a timely response.

As far as your problem goes

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[John W. Vinson]

Alguien habla castellano?

Trata microsoft.public.es.access

John W. Vinson [MVP]

 

[Guest]

I still can't find the solution of problem :could not update,currently locked
normally I have front end and beck end, on both sides the parametars
-shared-record locking-edited record and open using record level locking
in normal situation (multi user,app.10-15) if two users try to use same
record conflict dialog is appear and I use marking of row as already in use
but acording to microsoft there is a bug in microsoft access
http://suport.microsoft.com/kb/331594/en-us
when the same user try to do a second edit attempt in a transaction
this means that the same user blocking themselves, not other user
because if other user is blocking the editing it will be diferent message

microsoft have the solution of that problem - use microsoft OLEDB driver for
ODBC (MSDASQL) instead of Jet 4.0

but I have difficulties to use anthying else but Jet, simply it is new for
me I don't know what to expect and I think that it can't be done with access
on the both side(as database part and frontend)
is there any other solutions ,like some version of me.undo if the user is
start editing and this proces is blocking
I must say that this error cause blocking this computer and nothing can be
done other then wait or end task ,problem is when one computer often have
this error and then he cause other users to slow down their transaction
because the mdb on back end is to busy?

sorry,for my english


Korisnik "David Portwood" napisao je: