xxdesmus
asked on
Max number concurrent network users in Access 2003?
We have a Microsoft Access 2003 database that is being used over a corporate intranet. We have several locations that are spread out over roughly a 150 mile area. The database is being served from roughly the middle of that area (Central). We have users that are roughly 70 miles West of the central office, and users roughly 80 miles to the East of the central office.
We have approximately 45 concurrent users accessing this MC Access 2003 database over the network from a combination of these three regions (East, Central, and West).
The "Active" database is approximately 45MB, and the "Archive" database is approximately 180MB.
My questions are:
What are the generally accepted limits on a MS Access 2003 database over a network?
What is the maximum size that a MS Access 2003 database should be if it's being used over a network?
Let me include that I am well aware that MS Access 2003 databases are not meant to be used over a network. I am just an intern who is here for 2 months. I am trying to work with this corporation to move to a significantly more efficient platform (such as MS SQL, Lotus Notes dB, or Oracle), but at the moment they are stuck with MS Access 2003.
Thanks for any suggestions everyone.
We have approximately 45 concurrent users accessing this MC Access 2003 database over the network from a combination of these three regions (East, Central, and West).
The "Active" database is approximately 45MB, and the "Archive" database is approximately 180MB.
My questions are:
What are the generally accepted limits on a MS Access 2003 database over a network?
What is the maximum size that a MS Access 2003 database should be if it's being used over a network?
Let me include that I am well aware that MS Access 2003 databases are not meant to be used over a network. I am just an intern who is here for 2 months. I am trying to work with this corporation to move to a significantly more efficient platform (such as MS SQL, Lotus Notes dB, or Oracle), but at the moment they are stuck with MS Access 2003.
Thanks for any suggestions everyone.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Documentation? If there WAS any you'd only be complaining that it wasn't up-to-date! (:-)
You have the sympathies of all here - we've all been there!
You have the sympathies of all here - we've all been there!
I just wanted to toss in addition to Peters solid response that indeed it does sound like a solid app. To perfrom well with 45 users with a typical read/write app is great.
However what I wanted to voice a little more strongly is running it over the WAN; a definte no-no. I would have thought they would have had issues with it already.
But since it does sound like a very solid app...
<<but unfortunately the developer did not leave much documentation in his code (he has since left for another job) and I am not much of a Visual Basic developer. >>
VB is not all that hard to pickup along with a little help here. I'd say take a stab at it. Never hurts to expand the skill base, especially when someone else is paying<g>.
As long as you've been up-front with them about your skill level, then I'd say go for it!
JimD
ASKER
Hi Guys, I appreciate all of the assistance. I would not usually mind digging in and learning some Visual Basic, but the problem is that I am only going to be in this position for another 1 1/2 weeks. So I am at the point where it doesn't really make much sense for me to try and power-learn Visual Basic so I can just begin to troubleshoot this database right on my way out the door.
Now, if they were to offer me a full time position, then I will definitely be back to this forum to learn some Visual Basic :)
Oh, and they are already getting the ball rolling in regards to moving this database (and front end GUI) over to a platform that will be supported by IT. Right now MS Access dB's are not supported by IT, and hence they fall into my department (where they then fall into my lap). Whether or not to put in the time and effort to remediate this current dB )and front end GUI), or to just scrap it and move forward with a different platform is a decision that the upper management needs to make
Now, if they were to offer me a full time position, then I will definitely be back to this forum to learn some Visual Basic :)
Oh, and they are already getting the ball rolling in regards to moving this database (and front end GUI) over to a platform that will be supported by IT. Right now MS Access dB's are not supported by IT, and hence they fall into my department (where they then fall into my lap). Whether or not to put in the time and effort to remediate this current dB )and front end GUI), or to just scrap it and move forward with a different platform is a decision that the upper management needs to make
One can reduce connection load on database by closing conncetions on each database trip instead ofusing a global open connection in client applications. that may be seen in web pages in which connections are opened in start of page script and closed on end of script page.
So one can open connection on each database trip and close imeditiealy database process is over on the button, form etc.
That will be most efficient programing technique.
Keeping a global open connection will increase no of connections to the database file.
So one can open connection on each database trip and close imeditiealy database process is over on the button, form etc.
That will be most efficient programing technique.
Keeping a global open connection will increase no of connections to the database file.
<<So one can open connection on each database trip and close imeditiealy database process is over on the button, form etc.
That will be most efficient programing technique.
Keeping a global open connection will increase no of connections to the database file.>>
This is actually a bad idea with a JET based backend as it causes the backend to repeatedly opened and closed, which with JET incures a significant amount of over head because it is file share based. I'd only do this as a last resort as performance will suffer.
With something like SQL Server etc, then yes, closing connections as quickly as possible is the way to go.
JimD.
That will be most efficient programing technique.
Keeping a global open connection will increase no of connections to the database file.>>
This is actually a bad idea with a JET based backend as it causes the backend to repeatedly opened and closed, which with JET incures a significant amount of over head because it is file share based. I'd only do this as a last resort as performance will suffer.
With something like SQL Server etc, then yes, closing connections as quickly as possible is the way to go.
JimD.
It is not problem for JET to open and close connections frequently. as it happens with website backends with undefined numbers. The restriction on number of Connections shall be solved or optimised for more numbers that might not act simultaneously and continusly, with this stategy,
We cannot gauarranty about query speeds, data transfers than SQL server or other powerfull databases.
We cannot gauarranty about query speeds, data transfers than SQL server or other powerfull databases.
rajanlengde,
<<It is not problem for JET to open and close connections frequently. as it happens with website backends with undefined numbers.>>
Yes it is. JET is not a client server setup but file share based. Every time you open and close the backend MDB on a server, the server and Access/JET on the client side go through a lot of house keeping chores to do that.
<<as it happens with website backends with undefined numbers.>>
This does not apply to web based situations because when using a JET backend with a web site, only one user is connected at any one time to the database and that is the web server. The number of users hitting the web site does not matter as it is the web server that talks to the database. Each individual user does not.
JimD.
<<It is not problem for JET to open and close connections frequently. as it happens with website backends with undefined numbers.>>
Yes it is. JET is not a client server setup but file share based. Every time you open and close the backend MDB on a server, the server and Access/JET on the client side go through a lot of house keeping chores to do that.
<<as it happens with website backends with undefined numbers.>>
This does not apply to web based situations because when using a JET backend with a web site, only one user is connected at any one time to the database and that is the web server. The number of users hitting the web site does not matter as it is the web server that talks to the database. Each individual user does not.
JimD.
I was t say frequesnt open and close of conncetion to jet happens in case of website although it may be from one user but connection close and open happens on page loads and works fine untill database file is small sized.
So connection close/open shall not much great problem in file share too from different users.
Jet will not keeping info about which user last opened/ closed and and doing one at instance.
If you keep open connections from many users it may hangout with too many connections. as that requires diffrent status locks information to be tracked for each connection.
It may be confusion over users and connections accessing the DB.
If we use global connection in client app then 50 Clients may open 50 simultaneous connections.
If we use form level or procedure connection open an close immediately then No of open connections will be reduced considerably may be 1-2 at a instance depending on work load usage pattern of users and application design.
Altough using better foolproof daabase as sql server is wise option.
So connection close/open shall not much great problem in file share too from different users.
Jet will not keeping info about which user last opened/ closed and and doing one at instance.
If you keep open connections from many users it may hangout with too many connections. as that requires diffrent status locks information to be tracked for each connection.
It may be confusion over users and connections accessing the DB.
If we use global connection in client app then 50 Clients may open 50 simultaneous connections.
If we use form level or procedure connection open an close immediately then No of open connections will be reduced considerably may be 1-2 at a instance depending on work load usage pattern of users and application design.
Altough using better foolproof daabase as sql server is wise option.
ASKER
The developer used ADO to handle the data transfer, and as far as I am aware, it only pulls down the records that each individual user needs.
My problem here is that I have been put in charge of trying to fix some of the issues we have with this database, but unfortunately the developer did not leave much documentation in his code (he has since left for another job) and I am not much of a Visual Basic developer. Great situation to be in, right? "Fix this problem even though you don't have any of the necessary tools/skills."