record locking issues and upgrading to 2007 or SQL

[Will]

I have an Access 2003 backend which started with one user in 1996 with Access
95, upgraded to Access 97, and then to 2003. There are approx. 5-15 Access
2003 front-ends now accessing the backend simultaneously throughout the day.
(We own 30 user licenses). I know the limitation with Access is about 5
simultaneous users so for many years we have gotten away with the limitation,
but for several years now we have started having locking issues. I get all
users out, it closes the .ldb file, and everyone can log back in and continue
working.
My question is: Would upgrading to Access 2007 solve the limited number of
simultaneous users accessing the back-end with record locking issues, or do I
need to commit to upgrading the backend to SQL server 2005? Would that even
solve the record locking issue? What is the new maximum number of
simultaneous users with Access 2007? Has Microsoft even stated that?
Further, I’ve never really been sure it was the max users logged in causing
the record locking issues, but it seems logical. I’ve tried using the LDB
viewer to analyze the situation, but it isn’t of much use. We can work for
weeks or months without a record locking issue, or we may have to get
everyone out of the database two times in one day. It isn't consistent. The
back-end is 138 MB, and has one patient table of 250,000 records with a
tblExams containing 464,000 records. It started out as a log book, but ended
up as a billing and tracking application.
Thanks in advance for any input.

 

[Jaz]

Hi Will;

I am by no means a genius and I am only telling you this as it happened to
me. Can't even begin to explain why, but if you keep your front end in
Access 2003 and leave your back-end in 97, it will magically work and no
longer lock

It worked for me. Unfortunately took about 5 years of my life away, however
nonetheless, IT WORKED.

GOOD LUCK.

 

[Will]

Jaz,

Thanks for your quick response. That's very interesting and something to
think about. That would be almost impossible though after 10 years since so
many changes and additions have been made to the tables attributes since 97.

Will definitely put that back on the table to think about though. Appreciate
it very much.

Will

 

[Larry Daugherty]

The number of users isn't the issue when they each have a copy of the
Front End on their desktop.

Are you distributing those Front Ends as MDB or MDEj? I recommend
going to strictly MDE for the users.

In a "lock" situation, when you have the users out, what do you
actually read in the LDB? "isn't of much use" isn't very specific.
It might be worth tracking over time. You could have any number of
causes but the most common is user twiddling the design, opening
exclusive, even hacking directly into the tables in the Back End..,,
the next most common cause is a flaky Network - most usually a NIC on
the problem computer.

Google these Access groups for various search terms related to
"maximum users". In a properly designed system you'll have used your
last existing Access license before you hit a limit.

HTH