Microsoft Access DB with multiple users

Posted on 2004-10-21
Last Modified: 2008-03-17

I am using Microsoft Access 2000 to create a rather large database(by my standards).  The database will sit on my network. Up to 16 people could be using the databas at any given time.  Will this pose a problem?  I need the database to run flawlessly or as close to it as possible.  I was considering breaking up the database into 3 and having only about 4-5 users at any given time.  I know the database can handle the number of users on paper but I would rather have input from someone with real life experience.  I don't want to take any risks!!!

Here are some specs on the size of the database...if that makes a difference.

It has a main form and about 4-5 subforms.  There are about 400 records in the main form and each related subform contains about 60 records.  When I complete  a large query I am near the limit of 255 columns.

Thanks Jim
Question by:jdolan2587
    LVL 41

    Expert Comment

    It depends on how many concurrent users you have, and how many concurrent updates and edits they will be carrying out. Is there a significant risk of users editing the same record at once?

    If most of the user are reading and not editing records, you should be ok.
    LVL 44

    Expert Comment

    first you should strongly consider 'splitting' the database into a Front-End,with all the forms, reposts queris etc and LINKS to the Data tables whkch are REAL tables in the Back-End.  There is Wizard in Access that can simplify the process for you.

    This is actually a rather small database. I built an app, a couple of years ago, that had several thousand records, and several forms, and multiple reports, and about 50 potential users, and NEVER had any corruption issues (just lucky I guess, or so I have been told).  This was split into the FE-BE model that I mentioned above.

    LVL 41

    Expert Comment

    AW, was that an mde or mdb front end? I'm told that mdes are far less prone to corruption as well - I've never really used them except for small standalone dbs as I tend to make use of some features that don't work in mdes.

    Author Comment

    Thanks for your quick response.  

    All users will be making edits but it is unlikely that they will be editing the same record.  

    I have created the datbase for elementary report card grades so in most cases only one teacher will be editing his/her class (25 records or so).  In some cases there are 2 or 3 teachers but it is unlikely they will be working on the same student or record at the same time.

    For some reason I feel more comfortable breaking the database up into 3 seperate identical databases but I don't want to make more work for myself in the end if I don' have to.  I know that this is not the proper use of a database but my advanced database knowledge is limited.  

    I am unfarmiliar with front end and back end design.  

    LVL 41

    Accepted Solution

    LVL 44

    Assisted Solution

    Shane, I have never used MDEs - to many headaches when changes to the FE need to be made.  

    Jim, there is nothing to 'design' - you simply treat the SINGLE MDB as normal, except that the REAL tables are physically located in the BACK-END and linked to the Front-End which has ALL of the Froms, Reports, queries etc.  The FE behaves EXACTLY as if this were a SINGLE database.  There are no design 'issues'.  YOu build the app as a SINGLE mdb, and at the very end, before giving it to the uers, run the DataBase Split wizard, and there you are.

    Once you understand the process, you can the n BUILD the MDBs from scratch, as a split App, but that is for more experiences developers.  Most of the time, the easiest appraoch is to SPLIT as the last step in the development process.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now