Results 1 to 7 of 7

Splitting an Access database for Multiuser environment

  1. #1
    jjc9809 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    1

    Splitting an Access database for Multiuser environment

    Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability

    Provided by Aparna Pophale, Quality Assurance Specialist

    Microsoft Access lets you easily create databases to store and present your data in forms and reports. When starting, a database may be very simple and trivial, but over time, it may become more critical as you add more data, features, and even share it with others. It gains a life of its own and the overall design becomes critical.
    One of the most important architectural designs is splitting the database into a front-end and back-end database. This is the way Access was designed to let you support multi-user databases and significantly simplify how you enhance the application over time.
    A Split Database Design: Front-End/Back-End Databases

    Splitting a database is a relatively simple concept. You take an existing Access MDB/ACCDB database with its tables, queries, forms, reports, macros, modules, etc. and divide it into two databases:
    • The “Back-End” database just contains the tables
    • The “Front-End” database contains the application objects (everything except the tables) and links to the tables in the back-end database
    This design is especially useful in multi-user environments where the back-end database is stored on a network and contains the shared data. Each user then has a copy of the front-end database on their desktop pointing to the shared database.
    In multi-user environments, the front-end database can also contain tables that are private to the user. These local tables can store the user’s settings, selections, temporary or intermediate tables for processing data or reports, etc.
    Reasons to Split a Microsoft Access Database

    Here are some of the major reasons to use a split database architecture
    • Without a split database architecture, you’ll need to update the database with the latest data, people have changed with every new release.
    • Application enhancements are simplified since they are made in the front-end database without worrying about changes to the data in the back-end database. Releasing new versions and bug fixes becomes much easier since only the application part needs to be distributed. Of course, if you modify table structures or add/delete/rename tables, you’ll need to apply those changes to the back-end database.
    • Performance can be significantly enhanced and network traffic reduced when the user has a copy of the front-end database installed on their desktop rather than running it off the network each time they use it.
    • Temporary tables can be kept for each user in their front-end database. This avoids collisions among multiple simultaneous users if they were all using one database.
    • Without splitting a database, multiple users running the same database on the network increase the chance of database corruption. The split database design minimizes this problem and avoids code corruption from impacting data corruption.
    • This simplifies database administration since the data is stored centrally and can be backed up and compacted. A single master front-end application database is copied to each user’s machine, but is not necessary to back up.
    • Provides an opportunity to expand a database size beyond the 2 GB size limitation of Access since the front-end database can link to multiple back-end databases if necessary.
    • Sets the stage for migration to SQL Server. If the application evolves to need the features of SQL Server, you can still use the front-end database to link to data stored in SQL Server.
    How to Split Your Microsoft Access Database

    You can manually split your database by:
    1. Copying it
    2. Deleting all the non-table objects from one of them and make that your back-end database
    3. Delete all the tables from the other “front-end” database, then link to the tables in the back-end database.
    Or, you can use the Microsoft Access Database Splitter Wizard to split the Access database. Consider this example:
    Open a Tasks template form Microsoft Access 2007. The Tasks database is designed with tables, queries, forms and reports. The database contains 3 tables Contacts, Filters and Tasks. To open a Database Splitter, select Database Tools tab from Access ribbon and click on Access Database option.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,887
    And the best link I've found on multi-user is: http://www.accessmvp.com/TWickerath/.../multiuser.htm
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Jocie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    2

    Question on on-going front-end updates

    I found the Dec 2011 post on splitting an Access database really helpful. I created a database for my office that has become increasingly complex & now has 10's of thousands of records in it in several tables. It is set up as a multi-user database, but it is not split and not surprisingly, performance is getting worse & worse & we have had several corruption issues. It sounds like I need to split it for sure.

    My question is (& I couldn't find anything that addresses this), if each of my users (about 8-10) has a local FE on their machine, what happens when I create new queries, need to update forms, etc? Does this just happen on my local copy & then I need to copy it to each users computer? This DB has been a very dynamic tool & I don't expect that to change any time soon & I am concerned about the technical issues that regular updates to the front-end might cause.

    Any input on this issue would be much appreciated!

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,370
    Usually there person in charge of the database keeps a "master" copy of the FE. If a change needs to be made, a copy is created of the master, each are renamed to keep version integrity, the new one is modified, and a copy is sent to all users. The users then delete their front ends (or store them as backups) and use the new ones.

    An issue that arises with this is if the users had tables and queries created locally in the FEs. In this case, each user would have to import the objects that were specific to their FE from the old one to the new one. Access has an import feature to make this easy.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,887
    There are also FREE updating tools that will assist in automating the update process: http://www.btabdevelopment.com/ts/freetools
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    Jocie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    2

    Thank you...

    Thank you for the replies! I am sure I will have more questions as I initiate this process, but that is helpful to know as I move forward.
    Jocie

  7. #7
    wayneroyce is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    1
    way cool. I am completely new to Access building, and this is exactly what I was wondering on how to handle my multi-user functionality. I will have something to post when I get further in the project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Splitting my database.
    By bcox9 in forum Access
    Replies: 7
    Last Post: 12-17-2011, 04:23 PM
  2. Access Environment change Problem
    By accessvuri in forum Access
    Replies: 2
    Last Post: 04-15-2011, 12:14 AM
  3. Splitting Database
    By injanib in forum Database Design
    Replies: 0
    Last Post: 02-25-2011, 09:08 AM
  4. multiuser for access database
    By cupidleomanoj in forum Access
    Replies: 4
    Last Post: 09-28-2010, 09:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums