none
Microsoft Access Database Scalability: How many users can it support?

All replies

  • Dear Luke,

    I agree with you that Access can handle hundreds of simultaneous users. We need to do stress testing because it seems that Microsoft does not provide the official document for us.

    Regards,


    Be happy.
    Thursday, June 09, 2011 2:20 AM
  • With Access 97 the biggest problem prevent a large number of users from shaing a backend was the dreaded "Disk or Network Error" which could not be recovered from.  One way around this was to to put the shared backend on a machine in the local loop.  The problem of course is that an Access backend is just a file sitting on a server -- all data has to be pulled accross the network and processed on the local machine.

    Another reason that Access is not considered scalable is that many systems are true end user computing systems.  They were original built by a user to meet a local need and then someone decided to split them and put the backend on a server and the frontends on multiple users' machines.  IF the application is written for multi-user operation it will usually scale well -- unfortunately, it usually isn't.

    Finally, we come to the problem of disaster recovery.  An Access backend simply does not have the transaction reccovery built-in the way a server based database system does.  You have to grow your own or switch to a server based backend -- i.e. SQL Server.  Once you do that you have added a layer of manpower overhead.

    It is interesting to note that in Office 2010 x64 Microsoft significantly increased the size of Excel spreadsheets but left Access capped at 2GB.  You can draw you own conclusions about where Microsoft thinks Access belongs.

    Thursday, June 09, 2011 4:44 AM
  • Saberman,

    Thank you for your feedback. I think you're missing the point of my paper and where Microsoft Access fits in the enterprise. 

    Access fits between what people do with Excel and what enterprises do to create large, scalable, mission critical applications.

    Access scalability should be a secondary consideration of whether Access is appropriate or not. I would postulate that 99+% of Access databases (and desktop databases) never run into the 2 GB limit, so scalability is more of the exception than the rule. It's really hard for people to type that much information even after several years. Similarly, most Excel spreadsheets never get that big either. To drop a technology in fear that someone may create something wildly successful is inappropriate.

    Even if scalability were an issue and the 2 GB limit encountered, we agree that SQL Server is an appropriate storage platform for the data. In that case, Access is perfectly fine as a front-end to that. Are you suggesting that end-users should rely on others to create reports and view data that they could do on their own with Access?  Seems like a waste of professional developer time for those situations.

    I grant you that people create bad database applications. But I would say that is not technology specific. There are lots of terrible applications written with SQL Server, .NET, Java, Oracle, SAP, Excel, etc. The more important question is whether the technology and user were able to solve their immediate problem versus their alternatives at the time, and whether the organization can leverage what they built if it's successful. Even if it's decided to throw away what's already built, the fact that something exists is very helpful.

    My experience is that most organizations don't have the time or resources to build every database application that their line of businesses need. It's wrong to look at the applications that need to be migrated to a better developer or platform and say that it would have been better or cheaper to build it "right" originally:

    • Originally, the design would not have been known the way it is today. It was the actual evolutionary process for the solution to become what it is today. Interrogating the original author for his vision to get to today's functionality would be impossible even if you were allowed to use waterboarding.
    • The database evolution was partly controllable but mostly not. That is, the changes occured due to external forces unrelated to technology (e.g. the economy, competitors, new products, management decisions, etc.)
    • Only a small percentage (under 5%) of workgroup level databases ever need centralized IT involvement, and that's after several years
    • If organization don't allow information workers to create simple databases applications on their own, the IT department needs to create all of them for them when less than 5% should involve them. The costs would be astronomical and turn around times very long. It's basically giving opportunities to competitors.

    So yes, giving people matches may create uncontrolled fires, but that's why we have fire alarms and firemen. We don't tell people they can't use fire and force them to eat everything raw or pay a huge amount if they want something cooked. There is a middle ground. Organizations that manage this well gain a huge competitive advantage, and their IT departments really focus on what's strategic.


    Luke Chung President FMS, Inc. http://www.fmsinc.com
    Thursday, June 09, 2011 5:05 PM
  • Luke,

    great words! I can only wish our IT guys may think like you do. I'm really tired to lead this war. After a year of arguing I now have an understanding from our local IT dept, but still can't conquer head-office minds. 

    The one of my main arguments for my app was always the fact that we don't need IT support. And now when I'm asking for a stand-alone SQL Server db for it I just get an answer, "Why are you asking us smth while you were talking 'no it support'?"

    It seems that this holywar will never end. They don't think about security issues while a lot of employees keep their important information on a shared drive in Excel files. But immediately after moving (by myself) all this info into an Access database with my own security level or to SharePoint site with a perfect security tools, they begin to say that there is a bad security in my app, it's very easy to get an access to all the data. It sounds like a piece of... can't find a 'good' word... gibberish. And they never think about neither business needs nor all this time I've already saved for them delivering a working solution which meets these business needs.

    However, it seems to be an issue of all big companies and it will have place forever and ever. Thanks for your previous posts, I found some new arguments for a next battle in this endless war.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, June 09, 2011 8:55 PM
  • Hope you have more success with your battles.

    This is what I've told people in those situations.  Office/Excel/Access are the tools of infantry and special forces (not sure what the equivalent is in Russia). Go in quickly, get the job done, move on. Tactical operations, not meant to hold ground forever but to solve an immediate problem without getting the upper management and generals involved.

    That solves most of those issues where those tools are appropriate. However, there will be a non-zero percentage of situations where that approach is insufficient. In those cases, a good system is structured to anticipate and handle that. When infantry calls for air or tank support, they get it if the battle is planned properly. And it's usually provided with overwhelming force.

    I would contend that IT operations should have a similar philosophy. The big guns should be used when they are needed and worth the investment. When a bullet can solve a problem, why send in a tank? What's important is when infantry asks for support, it's ready and provided without blaming the soldier for asking for help. Properly planned and budgeted, an IT department can effectively support the front-line troops.

    They also have to anticpate and not complain when friendly fire and other mistakes occur. That's going to happen, but they can't panic over mistakes with bullets and treating them the same as mistakes with huge bombs and nukes.

     


    Luke Chung President FMS, Inc. http://www.fmsinc.com
    Friday, June 10, 2011 1:25 PM
  • Hi Luke ,

    I have gone through articles mentioned and have been a regular viewer of FMS site.

    I have been recently upgraded an split database Access 97 application to Access 2007 ( The back end databse's file size is around total 100 Mb and is in trusted location in a LAN folder ) . This application has two kind of users one with update access and other with only read access ).

    What is being observed is that

    1.  when a User with update access ( User)  enter a database file first another user with read access ( viewer)  can't even view the same database.

    The error popped up is <b>"Can't Use the database file location, File already in Use"</b>  error

    But two users i.e with update access can simultaneously enter the same database .

    What could be the problem here.?? I have checked the setting options like default "shared " mode, Default Locking "No locks" etc.. everything seems to be fine  

    But still it has very strange behaviour with respect to which kind of User is entering first in to the database.

    I need solution on this and dont want to Migrate to SQL cause I might end up in a more porblems.

    Could I get any technical help by you or any other technical expert Access guru in this   Forum.

    Thanks,

    Wednesday, February 01, 2012 6:06 PM
  • There could be several variables here. Is the database an MDB or ACCDB?  If it's an MDB is it the Access 2000 or 2002-2003 format? (the latter is better).

    Is everyone using Access 2007 with the latest service pack?

     

    Separately, I presume you mean the user isn't editing the back-end database and only their front-end database. In that case, each user should have their own front-end database that would only edit data in the back-end. Is that the architecture?

     

    When a user edits code in a database, that places an exclusive lock on the database that prevents it from being shared with others. Not sure if that's an issue here. Doesn't sound like it when you say two users with update rights can use it.

     

    Which then leads me to my suspicion that it could be related to workgroup security?  Is that how you are defining the rights?  If so, have you created a new workgroup MDW file in Access 2007 (or something after Access 97). You need to upgrade that too when you migrate from 97. It's been so long, I can't really remember that.


    Luke Chung
    President
    FMS, Inc.
    Wednesday, February 01, 2012 6:30 PM
  • Hi Luke ,

    Thanks for your reply.

    Data( back end Database ) and Front ends( ther are two fron ends)  of database both are in a separate folder in a network drive . Which is set as a Trusted location for all the Users.

    There are two  frontends one for User( with update acess )  and one for Viewer ( only read access ) both are in separate folders ( .accde files) and these folders are also in the same network drive.

    This structure was in Access 97 ( for .mde files front end and backend files ) and same structure is maintained in upgraded application( Data, Frontend files .accde format ) for   Access 2007.

    Its strange that everything is working fine in Access 97 applicaiton. And Yes lockfile is generated whenever User with update access enter first in database . But it never gets generated when viewer enters the database first and any number of Users  enters the same database later !!!

     

    Regarding my upgrade to 2007 I just used Upgrade option in 2007 , opened the old database and using Upgrade button upgraded the whole database files. ( Obviously I took care of all the pre conversion steps like compiling the database , linking files etc... and then upgraded it )

    But I never creatd new work group security MDW file .. I believe "User level security"  is no more in Access 2007 so do we still need workgroup mdw file in  Access 2007 ??

    Please clarify.. Thanks

    Wednesday, February 01, 2012 6:52 PM
  • Do your view only users have the right to create and delete files in the folder the backend is in?
    http://www.saberman.com
    Thursday, February 02, 2012 12:57 AM
  • No They should not have even delete and create , allowed to have read only access.

    Thursday, February 02, 2012 8:30 PM
  • How are you implementing the read access only front-end?
    Luke Chung
    President
    FMS, Inc.
    Thursday, February 02, 2012 10:56 PM
  • The Front end Application for read only users has VBA code used for each form  . Load form routine has disabled the edit as  me.edit.visible = False making viewers unable to edit the data in backend data.( This situation is reversed in update Users)

    The Linked Table objects are also Not Visible to these Viewers( This situation is same in update Users).

     

    Friday, February 03, 2012 12:16 AM
  • I suspect that for the current version of Access all users must be able to write/create/delete the ldb file if the database is to be shared.
    http://www.saberman.com
    Friday, February 03, 2012 3:46 AM
  • I am very intrigued by the work of your company and your approach to application building.  I work for a small pharmaceutical company that needs an improved time tracking system for the employees.  Our existing system was home grown with a web front end and a SQL back end.  The programmer has left the company and we are now paying someone to create another similar system.  This developer lives in another country and is difficult to get a timely response.  I'm an advanced Excel user and have dabbled in Access.  I feel I could work with an Access developer to quickly create an application better than what we currently have.  What I don't have a feel for is knowing what it would take to create a web interface (Access back end) for the 60 employees we have to access the system on our private network so they can post their work time to projects.  

    Thanks for any guidance you might provide.

    Les

    Saturday, March 01, 2014 4:44 AM
  • Hi Les,

    Sorry I didn't see this earlier. If we can still help, give us a call. There are lots of options to consider based on your objectives.

    For web sites, we would generally use SQL Server as the database. That doesn't mean we couldn't have a hybrid environment where an Access database could connect to that data to generate reports and perform other tasks that don't need to be done by the internet users.


    Luke Chung
    Microsoft MVP
    President of FMS, Inc.
    Blog Facebook Twitter

    Tuesday, September 23, 2014 7:30 PM