Too many connections to backend Access database

[Jerry Sadilek]

I have written an application which consists of a back end/front end database
on a network for many (up to 20) users. I am using Access 2003 (front end and
back end) and XP Pro.

I occasionally run into problems regarding maximum number of connections.
This problem occurs infrequently but, when it does, several users are unable
to log on. There are normally well over 10 users logged on at the same time.

Thanks.

 

[Ricky Hicks [MVP]]

Does each user have a separate FE file ???

R. Hicks

 

['69 Camaro]

Hi, Jerry.

I occasionally run into problems regarding maximum number of connections.

I suspect that the back end is located on a Windows XP Pro workstation, not
a Windows server, and a single copy of the front end is being shared by all
users. If this is the case, then replace the Windows XP file server with
Windows Server 2000 or Windows Server 2003 and place a copy of the front end
on each user's workstation.

Windows XP isn't designed to network more than 10 connections (which isn't
the same as the number of users), so that's where your maximum number of
connections limit is likely coming from. The most common cause of Access
database corruption is multiple users sharing a single front end file, so
make sure that every user has his own copy.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Jerry Sadilek]

Thanks for the quick reponses guys.

We are running an XP Pro workstation, and a copy of the front end resides on
each computer.

Regarding the concept of 10 users, I was just wondering whether this
actually applies to concurrency; ie. would this be a case of > 10 users
trying to make actual changes to database records at the same time, or simply
reading records at the same time, or simply being connected at the same time.

Thanks again.

 

['69 Camaro]

Hi, Jerry.

Regarding the concept of 10 users, I was just wondering whether this
actually applies to concurrency; ie. would this be a case of > 10 users
trying to make actual changes to database records at the same time, or simply
reading records at the same time, or simply being connected at the same time.

It's not the number of users, but the number of connections, that count.
For example, one user could open a single application that connects to the
file located on a Windows XP workstation and that one user could only make up
to ten concurrent connections (i.e., linking to the tables, connecting to a
specific table via VBA code with a separate Connection object, using a remote
query, Using Automation on the database file, et cetera, would each be
separate connections) before encountering problems. As soon as the database
tries to make the eleventh connection, the "too many connections" error
message pops up, even though there's only one user actually using the back
end database file.

Just opening the front end application may not be enough to connect to the
back end, though. Depending upon the application's design, the application
may not even connect to the back end until a bound form is opened, which
needs the data located in a linked table. In this case, at the point that
the form is opened is when the connection is made. For a visual cue, as soon
as you see the LDB file on the file server is when the first connection is
made.

Microsoft Access allows up to 255 connections to a Jet database file, so
this isn't likely to be your problem. If you want to eliminate this
possibility though, you could use the LDB Viewer to check the connections.
Please see the following Web page for links to the LDB Viewer and other free
diagnostic tools in the "Free Microsoft Access Troubleshooting Tools" section:

http://www.Access.QBuilt.com/html/links.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.