Limiting Concurrent Users

[mmccolgan]

I developed a run-time front end which interacts with an Access 2000
back end for data tables. It is used by a client with a number of
users in many different locations across a city, over 100 at peak
times. They are getting some "Save Errors" when there are lots of
concurrent users hitting the database at the same time, trying to edit
the same couple of tables. I am wondering if there are any suggestions
out there about stabilizing this problem? I was thinking that maybe if
we setup Access Security on the backend that there would be some way
to limit the number of concurrent users, but I cannot find any
reference to this in Microsoft Help or MSDN site. Is this something
that could be affected by the server-network connections instead of
the database itself?

 

[Arvin Meyer [MVP]]

You can limit connections to a file using Active Directory. That's probably
the fastest and easiest solution. Checking the user count in the server ldb
file would work as well. The toughest way, but should allow all persons to
edit, is to pad out the problem tables with enough default text data to
ensure that each record will use a 2K page (1.1K should do it). Then you
will never have to worry about users editing records on the same page. You
still cannot have 2 or more users editing the same record. That will be true
no matter how many users there are.

 

[aaron.kempf]

Wow.. that is probably one of the most interesting things I've ever
heard in this group.

Thanks!

I've been on several situations where i've followed all the best
practices.. and had similiar problems.

I just love how in ADP I never have a single problem with locking
anywhere. And if I did-- I could react to it by using the WITH
(NOLOCK) query hint.

But this-- about filling the pages-- is probably the only useful thing
I've read about Access MDB in the past decade.

Thanks

-Aaron

 

[aaron.kempf]

and honestly-- Move to SQL Server. Access never supports 100 users,
end of story.

The free edition of SQL Server (2005 Express) would probably easily
support than many users without purchasing any CALs or Software.

I'd just reccomend a multi-core server with SQL 2005 Express.. because
it is limited to a single CPU (as is Access) but SQL Server can react
quite favorably to a multi-core processor

-Aaron

 

[Arvin Meyer [MVP]]

But this-- about filling the pages-- is probably the only useful thing
I've read about Access MDB in the past decade.

Actually, the technique has been around since Access 2.0 and SQL-Server 4.2
(when Microsoft first bought SQL-Server from Sybase) and perhaps even before
then. Until SQL-Server 7.0, both Access and SQL-Server (and Oracle, Sybase,
and most others) used page-locking only, and large numbers of users would
continually have locking problems. Page-Locking is still faster than Row
locking (which Oracle introduced in the mid-1990's) but small rows from
large numbers of users continually wind up with locking problems.

So the trick was to pad out rows which had frequent updates to recent
records. If you could pad a row out to be large enough to avoid 2 or more
rows on the same page, you never have locking problems, and you emulate
row-level locking. Later, when SQL-Server followed Oracle's lead on
row-level locking, this trick was largely abandoned.

 

[Tony Toews [MVP]]

(e-mail address removed) wrote:

Note that Aaron's opinion is seldom shared by other folks.

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/

 

[bijou]

francais

 

[bijou]

parler francais