Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Simultaneous users - how many users can have a DB open at the same time

Posted on 2007-11-30
13
Medium Priority
?
292 Views
Last Modified: 2011-04-14
Hello there,

I have developed a simple Access database (recruitment) and we have entered all the necessary data. Now all entries have to be reviewed by the relevant departments and everyone may have to do certain modifications to the entries (the data, not the design of the DB objects), approximately 30 people at the same time.
Today I was told that some people - when opening the database - would receive a message that it was read-only. The DB is shared on a folder running Windows Server 2003, the permissions are correct (I asked them to try and copy a Word document into that folder, try to modify it and save it and it was possible), so it can't be that. I tried opening the DB myself (I'm the Admin) and I get the same error message.
As a result, I assume that this is an Access restriction (maybe total number of simultaneous users opening the DB). Or maybe something else? Maybe I have to create a Workgroup file? I'm not really an Access expert, so any step by step guide would be really ehlpful.
Thanks in advance.
0
Comment
Question by:ntossiou
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 20383433
Are users opening the db directly, instead of using a local front end?
We had a similar set-up a few years back, and there was a limit of about 10 simultaneous users, but that was an OS thing.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 20383442
Your solution, if you do not have local front ends, is to go to that model.
Create a db with everything in the back-end db except the tables, and then link to the tables in the back-end db.
0
 

Author Comment

by:ntossiou
ID: 20383444
Yes, the DB resides on a shared folder on a server, which is mapped as drive Z on the client computers through group policy, there is no local front-end.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 75
ID: 20383455
The max # of users is (theoretically) 255.

This error many times can occur if one or more users do not have FULL Access to the folder holding the MDB.

mx
0
 

Author Comment

by:ntossiou
ID: 20383458
So the procedure would be to move the tables to a new MDB file, put that on the server, give a copy of the other MDB file to all users and link the user MDB file to the tables on the server MDB file?
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 20383461
See previous comment.
Access can handle more than 30 users (per specs), although it is hardly the best db for that.  But you really do want to use a front-end/back-end schema.
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 800 total points
ID: 20383467
>>So the procedure would be to move the tables to a new MDB file, put that on the server, give a copy of the other MDB file to all users and link the user MDB file to the tables on the server MDB file?
Ed Zachary. Yes.
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 20383470
<<So the procedure would be to move the tables to a new MDB file, put that on the server, give a copy of the other MDB file to all users and link the user MDB file to the tables on the server MDB file?>>

  Yes, exactly.

JimD
0
 

Author Comment

by:ntossiou
ID: 20383489
Thanks for all your comments.
I will try it (hopefully tomorrow, if not Monday) and I'll get back to you!
0
 
LVL 16

Assisted Solution

by:Rick_Rickards
Rick_Rickards earned 400 total points
ID: 20383508
MX is right about 255 users so 30 shouldn't be an issue.  Some things that might help are....

Split the database so all users access the backend thru a front end.  My thought is that someone may be gaining exclusive access to your .mdb by opening up something in design view w/o realizing the chaos that they have then brought to bear (even after they have closed the object).  If everyone's running a front end then that will put an end to this problem.

To create front ends for your users to try instead of having everyone access the server's copy just copy the server's .mdb, delete all the tables than from your workstation tell access you'd like to create a new table (a linked table), point it to where the server copy resides and then select every table there as a linked copy.  Exceptions would be any table used as a temporary or hash table, (something that one could have locally and preferably so it doesn't interfere with or even involve another user.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 total points
ID: 20383517
Basically you want a split mdb scenario (industry standard). Front End (forms, reports, queries, vba code) and a Back End mdb (tables only).  You can use the Database Splitter to do this easily.  Then, each FE is linked to the same back end.  This alone many times will eliminate the problem you are having.

mx
0
 

Author Comment

by:ntossiou
ID: 20394489
Thanks for all answers, jerryb30 gets 200 points because he was the fastest (I hope it's fair).
Thanks again everybody.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20394991
Just glad you got the answer you were looking for.

Rick
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question