Details:
Access 2003
Default open Mode: shared
Default record locking: Edited record
Open database using record-level locking: True
Track name AutoCorrect: Off
Run permissions: User's

Split (FE/BE) application
BE located on "shared" drive
FE located on users' desktop
There is a persistent link that keeps the BE locking file open.

Complaint:
The application slows to a crawl when multiple users are editing any
data. (Even as few as 2 users, working on unrelated records, cause a
major slow down.) No complaints if there is only one user.
THANKS!
David G.

uh.. I came to the same conclusion about a decade ago.. Jet just isn't
designed to support multiple users.

move to SQL Server.. run a couple of wizards for indexing.. and presto-
chango, you're going to have 10x better performance.


On Aug 19, 7:07*am, David G. <Dweebe...@GMX.com> wrote:
> Details:
> Access 2003
> Default open Mode: *shared
> Default record locking: Edited record
> Open database using record-level locking: True
> Track name AutoCorrect: Off
> Run permissions: User's
>
> Split (FE/BE) application
> * BE located on "shared" drive
> * FE located on users' desktop
> There is a persistent link that keeps the BE locking file open.
>
> Complaint:
> The application slows to a crawl when multiple users are editing any
> data. (Even as few as 2 users, working on unrelated records, cause a
> major slow down.) No complaints if there is only one user.
> THANKS!
> David G.

Aaron:
Thank you for your comments, unfortunately I am stuck with the tools
at hand. Also, the circumstances and performance degradation indicates
that something is not performing as expected, not that the tool is
insufficient for the task.


On Thu, 19 Aug 2010 10:39:32 -0700 (PDT), a a r o n _ k e m p f
<(E-Mail Removed)> wrote:

>uh.. I came to the same conclusion about a decade ago.. Jet just isn't
>designed to support multiple users.
>
>move to SQL Server.. run a couple of wizards for indexing.. and presto-
>chango, you're going to have 10x better performance.
>
>
>On Aug 19, 7:07*am, David G. <Dweebe...@GMX.com> wrote:
>> Details:
>> Access 2003
>> Default open Mode: *shared
>> Default record locking: Edited record
>> Open database using record-level locking: True
>> Track name AutoCorrect: Off
>> Run permissions: User's
>>
>> Split (FE/BE) application
>> * BE located on "shared" drive
>> * FE located on users' desktop
>> There is a persistent link that keeps the BE locking file open.
>>
>> Complaint:
>> The application slows to a crawl when multiple users are editing any
>> data. (Even as few as 2 users, working on unrelated records, cause a
>> major slow down.) No complaints if there is only one user.
>> THANKS!
>> David G.
THANKS!
David G.

[klen_]

Hello all

Originally Posted by a a r o n _ k e m p f

move to SQL Server.. run a couple of wizards for indexing.. and presto-
chango, you're going to have 10x better performance.

+1


David, could you show us your database schema.
it is possible that some actions/queries can be run on the user's computer without any decelerations.
Instead of some queries you can use temporary tables.
For example:

	Dim tmp_mdb As String
 	Dim pdb As String
 	pdb = Environ("Temp")
 	
 	If Int(Val(Access.Version)) > 11 Then
 		tmp_mdb = pdb & "\tmp_123.accdb"
 	Else
 		tmp_mdb = pdb & "\tmp_123.mdb"
 	End If
 	
 	Dim db As DAO.Database
 	Set db = DBEngine.Workspaces(0).CreateDatabase(tmp_mdb)
 	...
 	...
 	db.Close
 	Set db = Nothing

Thanks Allen. Very helpful information.
In researching the problem I had come across the other responder's
comments regarding Access. I appreciate his taking the effort to
respond, but his prejudice or bias caused him to miss the point of the
question; which, you saw and responded to quite nicely.

I learned today that the network where the application is running is
basically several PCs running XP Pro, without any central server or
any Windows Server OS.

If I understand your comments, turning off "Record Locking" doesn't
prevent Access from handling edit conflicts. In the event of an update
conflict, the users sees the message about needing to refresh the
record and given the option of discarding his changes or overwriting
the other user's changes.

On Fri, 20 Aug 2010 10:24:58 +0800, "Allen Browne"
<(E-Mail Removed)> wrote:

>Hi David.
>
>Assuming a good, stable network that does not have excessive traffic, I've
>good quite good performance from A2003 on a wired Ethernet network (not
>WiFi.)
>
>My preference is to use 'No Locks' rather than edited record. We just train
>users how to handle the conflict dialog. Most of them never see it, but it
>does run better if you can do it this way.
>
>Record-level locking is also a bottle-neck. Unless you really need it, I'd
>strongly suggest you turn this off. (There are even cases where action
>queries executing inside a transaction won't run to completion if this is
>turned on, but run fine if you turn it off.)
>
>Beyond that, you may like to walk your way through the items in this Access
>Performance FAQ:
> http://www.granite.ab.ca/access/performancefaq.htm
>
>For your own sanity, just ignore the resident troll who regularly posts to
>deprecate Access.
>
>HTH
THANKS!
David G.

David G. <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> If I understand your comments, turning off "Record Locking"
> doesn't prevent Access from handling edit conflicts. In the event
> of an update conflict, the users sees the message about needing to
> refresh the record and given the option of discarding his changes
> or overwriting the other user's changes.

There are two issues there:

1. record-level vs. page-level locking

2. optimistic vs. pessimistic locking.

If you turn off record-level locking, the full data pages are
locked, and this can mean that editing one record can lock other
records stored inside the same data page. However, this is not
nearly as much of a problem as it sounds like it would be -- I never
use anything else, and my users don't have edit conflicts.

Optimistic/pessimistic locking refers to what happens when a user
lands on a record that is locked by another user. With pessimistic
locking, the user can't make any changes until the other user
releases the lock. With optimistic locking, the edits are allowed
and the database engine optimistically assumes that by the time the
edits are done, the record will be unlocked by the other user. This
is yet another case where it would seem that pessimistic locking is
the way to go, but you'll end up with much worse problems than you
do with optimistic locking. Again, I never use anything but
optimistic locking in all my apps, and users almost never report
edit conflicts.

If you're having concurrency issues with page-level locking and
optimistic locking, then you may need to re-architect your
application.

- Your schema may need adjustment so that tables are partitioned
differently.

- You might be able to improve concurrency with, say, random
Autonumber as PK (since tables are clustered on the PK, this means
new records won't be clustered at the end of the table, where edits
could be happening on the same data page.

- You might need to change you some of your forms to be unbound, or
to make sure they are dirty only for brief periods (i.e., if you're
editing in code, save quickly).

- You might be better off upsizing to SQL Server or another
server-based database engine, which is not file-based and thus can
avoid certain concurrency issues that happen with Jet/ACE back ends.

There are lots of things that can be done if you're having
concurrency problems. But it's not clear that your problems will not
be fixed by one or both of Allen's recommendations (switching to
page-level locking and using optimistic locking).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

David:
All of the forms have "No record locking" set in their properties. I
assume that the properties setting should over-ride Access's Options
setting for record locking. (Yes?)

I've been reading about Access 2003 record locking and how Access uses
page-level locking when Access is started by the application. (If the
application is started using Access's File --> Open commands, then
record-level locking is used.)

Since user's are clicking desktop shortcuts to open the application,
if the form properties don't over-ride Access's Options settings, it
would explain the slowdown experienced when 2 users are editing
different records.

The solution (if this is the problem) seems like an even bigger
problem. To protect the application the menu items are restricted
(Tools-->Options is not available.) Simply opening Access, without a
mdb being opened, disables the Tools-->Options menu item. Each user
would have to create a bogus mdb file, change Access Options, then
delete the bogus file.



On 20 Aug 2010 20:49:13 GMT, "David W. Fenton"
<(E-Mail Removed)> wrote:

>David G. <(E-Mail Removed)> wrote in
>news:(E-Mail Removed):
>
>> If I understand your comments, turning off "Record Locking"
>> doesn't prevent Access from handling edit conflicts. In the event
>> of an update conflict, the users sees the message about needing to
>> refresh the record and given the option of discarding his changes
>> or overwriting the other user's changes.
>
>There are two issues there:
>
>1. record-level vs. page-level locking
>
>2. optimistic vs. pessimistic locking.
>
>If you turn off record-level locking, the full data pages are
>locked, and this can mean that editing one record can lock other
>records stored inside the same data page. However, this is not
>nearly as much of a problem as it sounds like it would be -- I never
>use anything else, and my users don't have edit conflicts.
>
>Optimistic/pessimistic locking refers to what happens when a user
>lands on a record that is locked by another user. With pessimistic
>locking, the user can't make any changes until the other user
>releases the lock. With optimistic locking, the edits are allowed
>and the database engine optimistically assumes that by the time the
>edits are done, the record will be unlocked by the other user. This
>is yet another case where it would seem that pessimistic locking is
>the way to go, but you'll end up with much worse problems than you
>do with optimistic locking. Again, I never use anything but
>optimistic locking in all my apps, and users almost never report
>edit conflicts.
>
>If you're having concurrency issues with page-level locking and
>optimistic locking, then you may need to re-architect your
>application.
>
>- Your schema may need adjustment so that tables are partitioned
>differently.
>
>- You might be able to improve concurrency with, say, random
>Autonumber as PK (since tables are clustered on the PK, this means
>new records won't be clustered at the end of the table, where edits
>could be happening on the same data page.
>
>- You might need to change you some of your forms to be unbound, or
>to make sure they are dirty only for brief periods (i.e., if you're
>editing in code, save quickly).
>
>- You might be better off upsizing to SQL Server or another
>server-based database engine, which is not file-based and thus can
>avoid certain concurrency issues that happen with Jet/ACE back ends.
>
>There are lots of things that can be done if you're having
>concurrency problems. But it's not clear that your problems will not
>be fixed by one or both of Allen's recommendations (switching to
>page-level locking and using optimistic locking).
THANKS!
David G.

Arvin:
Thanks. I had come across the link below and didn't find any issues,
with the exception of the subdatasheet set to "[Auto]" for the backend
tables. The information indicated this really wasn't a problem unless
the Datasheet view was being used. All the forms are using Continuous
form view.


On Fri, 20 Aug 2010 18:08:48 -0400, "Arvin Meyer" <(E-Mail Removed)>
wrote:

>Rest assured that Aaron has no idea what he's talking about. He's an
>unfortunate individual who constantly trolls these groups.
>
>Jet is quite suited for Multi-user use and has been since it's inception.
>You problems can be associated with the database design, or with your
>network.
>
>First tackle the design. An excellent performance FAQ can be had at:
>
>http://www.granite.ab.ca/access/performancefaq.htm
>
>If after implementing those procedures, you are still running slow, start
>checking the network, and the user's permissions on the network. Users need
>pretty much all permissions except Full Control on the folder that hold the
>database.
THANKS!
David G.

David G. <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> All of the forms have "No record locking" set in their properties.
> I assume that the properties setting should over-ride Access's
> Options setting for record locking. (Yes?)

Yes, and that's optimistic locking.

> I've been reading about Access 2003 record locking and how Access
> uses page-level locking when Access is started by the application.
> (If the application is started using Access's File --> Open
> commands, then record-level locking is used.)

....if Access is set to use record-level locking.

> Since user's are clicking desktop shortcuts to open the
> application, if the form properties don't over-ride Access's
> Options settings, it would explain the slowdown experienced when 2
> users are editing different records.

It would mean they are using record-level locking, maybe.

> The solution (if this is the problem) seems like an even bigger
> problem. To protect the application the menu items are restricted
> (Tools-->Options is not available.) Simply opening Access, without
> a mdb being opened, disables the Tools-->Options menu item. Each
> user would have to create a bogus mdb file, change Access Options,
> then delete the bogus file.

I strongly doubt this is the actual cause of your performance
problem. Had you gone through the whole performance FAQ at the link
you were given earlier?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

David G. <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> I had come across the link below and didn't find any issues,
> with the exception of the subdatasheet set to "[Auto]" for the
> backend tables. The information indicated this really wasn't a
> problem unless the Datasheet view was being used. All the forms
> are using Continuous form view.

I don't know that that is true. I'd turn it off, anyway.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/