none
Multiuser Database in Microsoft Access RRS feed

  • Question

  • Hi,

    I want to create a Microsoft Access multiuser database. Requirements:

    - Have a foolproof system (two users can connect to the same form and the data is not going to be overwritten once they submit it).

    - I also would like to see the changes history, and be able to see who was the last person to modify a particular form, or tables.

    - I have heard about front-end and back-end in Access but don't know a lot about it in the latest version. Where can I find good material to learn how this works for a multiuser environment?

    This are the main questions and requirements. I am developing the database, if I can have input from this forum, it would be great!

    Thanks,

    Angel

    Thursday, June 9, 2016 5:41 PM

Answers

All replies

  • Access is not that cut and dry. It is a program that you will learn over time and then relearn over more time.

    Having said that, you can check out Wrox on the web. You can also look at web pages like

    https://msdn.microsoft.com/en-us/library/ms123401.aspx?f=255&MSPPError=-2147217396

    https://code.msdn.microsoft.com/

    http://www.thatlldoit.com/Pages/default.aspx

    http://accessmvp.com/

    http://www.eileenslounge.com/viewforum.php?f=30

    These are a few really good links off the top of my head to get started with. You can probably get through them in a couple of years before you run out of information.

    You can also review the previous posts in this forum. If you have specific questions please post them here and we can try to help.

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    • Proposed as answer by David_JunFeng Sunday, June 19, 2016 2:39 PM
    • Marked as answer by David_JunFeng Monday, June 20, 2016 2:36 PM
    Thursday, June 9, 2016 9:05 PM
  • You can find a list a very good resources at: http://www.devhut.net/2012/12/04/ms-access-where-start-learning-database-tutorials/

    The whole concept about a split database (Front-End (FE) and Back-End (BE)) is that you place the tables in the BE which gets place on a central server and then you distribute a personal copy of the FE (queries, forms, reports,... - the UI) to each user to install locally on their own PC.  The FE communicates back to the BE to display the data and permit multi-user access.

    Every multiuser database needs to be split.  Do not share a copy between users!
    Once you split your db, you need to create a persistent connection at the startup of your FE.
    I also recommend you setup an inactive logoff mechanism to boot people out after a given amount of time of inactivity within the db.
    Once you are ready to deploy your db, then you might wish to look into automating the process of pushing updates to your user.

    I hope some of this helps!


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Proposed as answer by David_JunFeng Sunday, June 19, 2016 2:39 PM
    • Marked as answer by David_JunFeng Monday, June 20, 2016 2:36 PM
    Friday, June 10, 2016 12:19 AM
  • I forgot to address you question regarding tracking changes.  For this you will need to develop/implement some sort of Audit Trail/Log.  If you Google the subject you will find some samples to inspire yourself from.  One I have used, and a very trustworthy source of information can be found at: Creating an Audit Log 

    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, June 10, 2016 12:22 AM
  • >>>- Have a foolproof system (two users can connect to the same form and the data is not going to be overwritten once they submit it).

    According to your description, you could refer to below helpful link:

    http://stackoverflow.com/questions/1672077/setting-up-an-ms-access-db-for-multi-user-access

    >>>- I also would like to see the changes history, and be able to see who was the last person to modify a particular form, or tables.

    You can create a procedure in Visual Basic for Applications that keeps an audit trail of the changes that are made to a record in a form.
    For more information, click here to refer about How to Create an Audit Trail of Record Changes in a Form


    >>>- I have heard about front-end and back-end in Access but don't know a lot about it in the latest version. Where can I find good material to learn how this works for a multiuser environment?

    About spliting an Access database, you could refer about Split an Access database

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Friday, June 10, 2016 1:27 AM