Change Control within Access 2016 as well as Development/prototyping vs QA vs Production environments RRS feed

  • Question

  • Without going in to much detail, suffice to say that I have 30+ years extensive IT and business experience in large scale and multi-platform/environments where separate development/prototyping, QA and production environments are non-negotiable as well as strict change control.

    I recently got involved in a community project and was it a logical choice to develop an application using Access 2016 and VBA. The last time I used Access and VBA was during the early '90's, thus there were a lot of learnings and lessons over the past 3 months. At this point in time I have a fully 3rd normal form relational database created with all the necessary referential integrity and relationships and all the core functionality in place, I still have some "bells-and-whistles" to complete. I am at a point now where I can or want to implement the application.

    I soon realised that change control may be an "issue" compared to the environments I am familiar with, e.g. moving changed data structures with or without data, codes changes etc.

    I'd appreciate any advice or suggestions as how to deal with this as it is a discipline I'd like to maintain.

    Monday, March 5, 2018 11:54 AM

All replies

  • If you are looking for source code control, you may want to see the below link for options:

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 5, 2018 1:29 PM
  • If all you are concerned about is code changes and if this application is being used in a multi-user environment, then you will need to:

    1. Split the database .accdb file into Front-end and Back-end files using the Database Tools --> Access Database wizard locating the resulting Back-end file on a shared network drive.
    2. Backup the application .accdb file.
    3. Create a .accde file from the .accdb file using Database Tools--> Make ACCDE. This will make it impossible for anyone to view or change any VBA code.
    4. Distribute the application .accde file to all users. Do NOT share a single .accde file among users. Each user must have their own copy.
    5. Make sure all users identify the location where both the Front-end and Back-end file resides as a Trusted Location using their ACCESS Options--> Trust Center settings.

    Any code changes will then need to be made in the original .accdb file only. After testing, you will then need to create a new updated .accde file and re-distribute it to all users.

    If this is a single-user application only, then it is still recommended that it be split, but only a single copy of the .accde file is distributed. Most developers still split the database even with only one user. In this case, the Back-end file may reside on the single-users PC and does not need to be located on a shared network drive unless you wish to. Trust Center settings are the same.

    Monday, March 5, 2018 5:12 PM
  • Monday, March 5, 2018 5:42 PM
  • Thanks to all that have responded, I do appreciate it.

    As the organisation is still small, for the immediate and foreseeable future I'll be the primary user as I am currently the Factory Manager (my retirement consultancy turned into a full-time job after 2 days). There may soon be another user with restricted update capability, and then maybe 2-3 users with access to reporting/reports (which is some of the "bells-and-whistles" I still have to complete). 

    I'll definitely be going the route of splitting the front and back-end. It is an integrated material requirements planning, job scheduling and stock control system I developed. I started off using Excel and VB but the demand for production grew rapidly and I was in urgent need of a more integrated and tightly coupled solution. As it is an initiative in a rural setting/town there was not funds to buy an "off the shelf solution", thus I decided to write a system as I was spending too much time trying to manage it with Excel and keeping things in sync. 

    Monday, March 5, 2018 10:20 PM
  • Hi SpottedCat,

    I can see that you got the help to solve your issue from the suggestions given by the community members.

    I suggest you to mark the suggestions as an answer which help you to solve your issue.

    It will be helpful for us to close this thread and It will also helpful to other community members in future who have same kind of issues.

    If you have any further questions then let us know about it.

    We will try to provide you further suggestions to solve it.

    Thanks for your understanding.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Tuesday, March 6, 2018 3:26 AM