Splitting Access database into multi databases

Posted on 2011-05-10
Last Modified: 2012-05-11
Splitting Access database that will increase accept concurrent users ?

For example I have one Access database include 30 tables if I distribute these tables on 3 Access databases and I make shortcut to all tables with orginal database and the users access tables from orginal database so this work will increase accept number of concurrent users than one database ?

Question by:egovernment
    LVL 14

    Expert Comment

    by:Michael Dyer
    Microsoft says that the latest version of Access Database (2003) supports 255 concurrent users, but in practice you will see that after 15 to 20 simultaneous users connect, performance will degrade.  Splitting the tables into multiple linked databases will not help.  Better to bite the bullet and move to SQL.  Even the free version of SQL Express will give you a much better performance with multiple users.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

    Splitting the data from the application is a good idea, and it is highly encouraged, especially for a multi-user database.  But splitting the data into multiple backends will not increase the number of concurrent users.

    I have been known to split my data into multiple backends, where data that is applicable to all aspects of the application is in one data file, and data that is particular to other aspects (HR, finance, operations) resides in separate data tables, but the only reason to do this is if you have a lot of data, and merging it all into a single backend would approach the Access file size limit.  And if you are bumping up against that limit, it is probably time to look into SQL Server or SQL Server Express.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "But splitting the data into multiple backends will not increase the number of concurrent users."
    And ... you will not be able to create Relationships among tables across multiple db's.  Relationships can only be created among tables inside a give db.

    And confirming ... no, it will not increase the number of concurrent users, which is a theoretical max of 255 for any single db.


    Author Comment

    Exactly I have the number of concurrent users is between 30 and 40 only and I will try to found a temporary solution can manage these users without any problems for one month because after that I will migration the data to sql server.
    LVL 10

    Expert Comment

    I would question where the data exists for the limitations on the number of users being around 30. We've run many tests and never seen that kind of degredation in performance. I think it's a myth from Access 2.0 days.

    A well designed Access database can support hundreds of users. Of course, what matters is the number of simulateneous users, and what they're doing.

    If everyone is just viewing data or entering data into a table, that takes very little work and a large number of people can be supported. If they are all running massive reports and queries wiith data updates, that can still be done but performance would be an issue (on any technology).

    If the back-end database is in SQL Server rather than an Access/Jet database, the number of users can be practically unlimited if each user has their own front-end copy of the Access application. Performance issues still apply based on what they are doing, and in many cases SQL Server is slower than Access.

    All that saiid, any Access application that is distributed to others with shared data should be a split database design. Here are a few resources we've written:

    Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability

    Significantly Improve the Performance of Microsoft Access Databases with Linked Tables

    We also offer a commercial product for enterprises, Total Access Startup, that helps with the distribution of databases to each user's desktop and launching the right version of Access. That may be overkill for your needs.

    Hope this helps.

    Author Comment

    Hi LukeChung-FMS
    Thank you for the answer

    I describe to you our system how is working and I will wait you to give us a feedback

    We split the access in two parts back-end (data) and front-end (other objects).

    The data itself availabe on server but this server not in the same building but on other building and connect with wireless network with speed 27mbps and there between 30 to 45 concurrent user connect with the database through network.

    The 2nd file include all objects like views/forms/reports/macros and modules availabe on each user client but each few days the database file was damage without know the resan.

    The users do all operation on the data like view / modify / add / delete / search / print reports depenednt on user permession or level.

    I analysis the data I found there 8 tables have a big number of records between 20000 to 80000 records and these tables the most using from users I suggest to split the main database file into 3 databases 2 of thems will include the 8 each file include 4 and the remain tables will be still on main database file.
    LVL 10

    Accepted Solution

    Disclaimer: I'm going to describe my personal experience deploying and supporting Access databases, and dealing with database corruption. It involves the use of some commercial products from my firm (FMS). For those who may be offended by this or consider it advertising, please don't be. There are free demos of the programs which may be sufficient to get through the questioners current challenges. This is what I consider our Best Practices. I welcome anyone else's suggestions.

    If you are suffering from corruption, that needs to be addressed separately from the scalability and performance issues you raise in your original question.

    First, I hope you have a disaster recovery plan in place. You may want to read my paper for what we consider best practices:Creating a Backup and Disaster Recovery Plan for Microsoft Access Database Applications

    Second, Access/Jet databases need to be periodically compacted to minimize corruption and bloat, and for optimal performance. The back-end database with the data is what needs to be compacted. You can do that manually. We created a commercial program Total Visual Agent:, that does it on a schedule with auditing and email notification if something goes wrong.

    Third, if you are experiencing corruption after regular database maintenance, it's often caused by a suspect connection/user who disconnects in an improper manner. That can be very difficult to detect and replicate. We have a commercial product, Total Access Admin, that monitors the people going in and out of an Access database, logs that activity, and flags the people that exit improperly.  If it's happening with the same person, there may be a hardware or network problem causing the corruption.

    Finally, it may be possible that the corruption and performance problems are due to the front-end application. Bad code and techniques, corrupt objects, and other issues may be causing crashes and problems that lead to corruption. We address this in a few ways:

    1. We adopt, implement, and detect/fix deviations from best practices techniques:

    2. We implement global error handling that records crashes by users to text files so we have evidence of what failed. In addition to the procedure call stack, current procedure, error number and description, I also want the line number:

    Hoep this helps.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Access Query 4 14
    Subform Link field 13 22
    restrict users from ODBC useage 5 13
    System Analysis 5 18
    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…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server views 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 Access…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now