none
Promoting backend changes RRS feed

  • Question

  • I am new to developing in Access, but not new to DB application development. Previously I have worked with separate instances for Development, Test, and Production. The metadata of objects changed in Dev were captured in packages and applied to the test environment for testing before being promoted to Production. This is the best practices for numerous applications (SAP, Oracle Financial, Banner® by Ellucian, etc.)

    My question is: Are there resources available to explain the software development lifecycle for MS Access development (in detail)?

    With the front end I just distribute a new copy to the user base after I develop the changes they requested. I have no idea how to manage changes to the back end. I am using Access 2013.

    The state of my project is that I am almost done with phase 1 of development and ready to deploy a production copy. I have not split the database yet. There are several other phases planned for deployment, which will involve changes to the database structure (new tables, columns, and relationships).

    Before I deploy the application I want to make sure I have a clear understanding of how I can deploy future changes. Phase 1 will be deployed to a user base and they will start entering data. Development of phase 2 will then begin. When phase 2 is deployed the production copy of the database will need to be updated without losing any of the data that was entered by the users of phase 1. Phase 2 will add users that will also be entering data. The same will hold true for phase 3 where database changes will need to be applied without losing any of the existing data.

    My previous roles were as an analyst, so the developer role is a bit new to me. I have an understanding of the concepts, but how to make it happen isn't always clear to me. Therefore I could use some detailed documentation.

    Thanks!!

    Monday, December 7, 2015 5:30 PM

All replies

  • Hi Jim. First, I recommend splitting your database now, so you can start testing it in a split configuration. For example, you may have to add a routine to check the table links and auto re-link them. As for making BE design changes later on, I wouldn't worry too much about it. Once you get to that point, there will be tools to help you, or doing it manually would not be so difficult. There's also the option of "migrating" existing data to the new BE structure rather than incorporate additional BE changes to the existing one. Unfortunately, I don't have any links to point to any documentation regarding this topic. I can definitely point you to some tools that may help in this situation though. Cheers!
    Monday, December 7, 2015 5:46 PM
  • Thanks DB guy,

    I follow all of your explanation. Is there a "best practices" approach? Migrating the old data to the new structure works OK for me.

    Can you post some links to the tools you are talking about? If it is Free it is for me, hopefully some of them are available for free.

    Thanks for the help!

    Jim

    Monday, December 7, 2015 5:52 PM
  • As far as I know there is no formal way to handle such upgrades. The longer you work with MS Access, you realize it is just not oriented toward serious Enterprise development.

    As you state there are two basic types of update (1) those that affect only the front end and (2) those that affect both. There might be a third that affects the just the back end and not the front end but that is rare. An example might be where you just need to increase the length of a text column.

    Each user should have their own copy of the front end, so here is another problem: how to ensure each users front end gets updated when you make a change? There are various automated ways to do that, which you can Google.

    For back end changes, I usually come up with either a conversion module or possibly just an update query to change the data in one fell swoop. I always make the changes after close-of-business or on the weekend when no-one is using the database. Even here I come across the problem of some user logging off while leaving the database open which prevents any updates.

    Backing out is all but impossible since database changes will have been made based on the new back end which may be incompatible with reverting to the old back-end.

    Sorry I could not be more positive, maybe someone else has devised a magic solution.

    A lot depends on what changes are being made between versions, some are easy to handle and some more difficult. By the way, there is nothing to stop you have multiple different front ends for different user groups.




    Monday, December 7, 2015 6:00 PM
  • Hi Jim,

    I think the "best" best-practice is to have all backend design completed before deploying the application because modifying and deploying the frontend is not a problem. However, I realize it's not possible all the time. As I was saying though, it doesn't matter which method you choose to use because when then time comes, you will not have any problem incorporating your changes to the production copy (or upgrading the production version into the new one). The following link has some tools that might help with some of the tasks you may have to deal with: Free Access Tools

    Good luck!

    Monday, December 7, 2015 6:44 PM
  • ATGNWT,

    My situation is fairly simple. I work for a small company and anybody that would use the application is in the same room as me (only about 5 users). So getting everybody out of the app wouldn't be much of a problem. Most of the changes to the BE would probably be adding columns and maybe a few tables (with relationships).

    With a module or query, I imagine it would be included in the new FE. The old FE/BE is on a network share, while the new FE/BE is on my hard drive. The module or query would need to point to the old BE and copy the data to the new BE. The new BE could then be moved to the network drive and the FE distributed to the users. Is that the process  you would use?

    In my current role I am a jack of all trades and master of none. I am the server admin, network admin, desktop support, and software developer. All much more technical than my previous roles. I have a server available to me on premises.

    (To anybody reading this) What are your thoughts on using an SQL Server Express as the BE? How much more complicated is that?

    Jim

    Monday, December 7, 2015 7:04 PM
  • Jim,

    When I used mdb/accdb file as backend. I have 2 backend field:

    #1 dev version for working on new functionality etc.

    #2 pattern with actual data structure

    pattern have table: dbVersion with one field version.

    When I changed dev db i compare dev with pattern (can use Database Compare 2013). 

    I write query to change db-> pattern (see: DDL SQL)

    I have #3 file with upgrade module with 1 table updateScript and field:

    1. dbversion

    2. sort

    3. sqlscript

    Only need you must do is: write sql sripts in table, and execute on client db with correct order.


    Michał

    Monday, December 7, 2015 7:10 PM
  • (To anybody reading this) What are your thoughts on using an SQL Server Express as the BE? How much more complicated is that?

    Jim

    Hi Jim,

    Access can connect to different types of database, and SQL Server (Express) is just one of them. If you need better security or your data file will exceed 2GB in size, then using SQL Server (Express) as your back end makes more sense.

    Just my 2 cents...

    Monday, December 7, 2015 8:57 PM
  • Well assuming you are going to test your changes before the deployment, here is what I do. I make a copy of the front end and back end on my local box then relink them. Then I make all my changes and test them.

    Then when no-one is going to be using the database for the period of time I think it will take to implement (could be anything from 15 minutes to several hours), I backup the current FE and BE then replace them with my new versions and relink them back (keeping FE and BE named the same as before).

    In implementing, you have 2 choices, use the existing BE and replicate your BE changes in it, or use your new BE and have to refresh the data.

    Which choice you make depends on what the changes are and how easy each option is. In either case, you have to retest the application when you are done.

    Monday, December 7, 2015 9:18 PM
  • With a module or query, I imagine it would be included in the new FE. The old FE/BE is on a network share, while the new FE/BE is on my hard drive. The module or query would need to point to the old BE and copy the data to the new BE. The new BE could then be moved to the network drive and the FE distributed to the users. Is that the process  you would use?

    Hi Jim,

    Essential is the splitting in FE and BE.

    In developping dynamical databases the data definition of the BE may change. When I modify an application in the develop mode, all changes to the data defintion are logged in the FE.

    On installation of a new version in the production mode, it is recognized during the first start up that there are logged changes. These logs are then "replayed" on the production BE.

    This way of working functions very well for about 90 different applications, without the need of heavy version control. In fact the logs are kind of version control.

    Imb.

    Monday, December 7, 2015 9:50 PM
  • Assuming a split database, then you dev system is a copy of the front end, and the back end.

    Changes in the front end are quite easy. You can create new forms, new code etc. When it comes time to deploy the next great version, then you link your front end to production, compile down to an accde, and then distribute that new front end to each user (you already noted you grasp and do this). Often one can cobble together some kind of update system. On startup, you can for example read info from a “version” table in the back end. If you then need to update the front end, then some kind shelling out to a batch file and an exit of the front end.

    Back end changes are more of a challenge. Hopefully not a lot of table and design changes are required. I generally just open up notepad or word, and every time I make a change, I simply make a quick note in the file

    Eg:

    tblCustomer – Add CellPhone field

                      Add FaxNumber  field

    tblInVoice  - Add Approved field

    I also don’t make notes as to the data type or even the length in above, since “when” I make changes to the production database, I have the dev back end also opened, and can look at the above fields and see the type, length etc.

    So right before deployment time, I open up the production back end, and make the above 5-6 table changes. This only takes a few minutes. However, such structure changes MUST be done when users are out of the application (so you schedule this downtime during lunch or whatever).

    Once the table changes are made, then you roll out the new front end. And because it is an active application, then usually the table changes are not too extensive. (when you first start development, then LOTS of table changes occur - but by deployment time, table changes tend to be minor - at least we hope so!).

    Another approach is to write VBA code to update the back end tables. This means you NEVER use the table designer to add or change back end strictures, but place code in an update module. (perhaps with some version number).

    Thus on deployment, you first run this update code. The issue/problem with this approach is writing such update code can be challenging. (I eventually built some code that is driven by a table in the front end).

    However, for most cases, just jotting down the table changes you made as you develop tends to work quite well, as 4-5 table changes can be done in only a few minutes of your time.

    As noted, you could certainly adopt a free edition of SQL server for the back end. Then during development, anytime you use the SQL table designers, there is an option on save to “script your changes”. Thus each time you cut + paste the script code into one “update” file. Then right before deployment you run this script on the SQL server side.

    Anyway, the above “notes” approach has worked well. The 2<sup>nd</sup> approach of VBA code to modify things was used in cases where we deployed software to many customers, and we could not always be on site.

    So the simple notes approach does suffice quite well, especially for cases where you work on site.

    Regards,

    Albert D. Kallal

    Edmonton, Alberta Canada

    Tuesday, December 8, 2015 4:06 AM
  • When I modify an application in the develop mode, all changes to the data defintion are logged in the FE.

    lmb,

    Is this a manual process similar to Albert's below? Or is this a function of Access that happens automatically. I would be interested in learning more about the process you use. I'm just beginning in the developer role so I may need some details on how to do it. Do you have any documentation or can you point me to the resources you used to develop the process.

    Thanks!
    Jim

    Tuesday, December 8, 2015 3:43 PM
  • Thanks Albert,

    This is kind of the default process I was thinking of if there isn't a tool available to export and import the metadata for a table. Fortunately I think the subsequent phases of my project will be mostly FE changes and I will only add a few tables and columns, so this may be the shortest path to success (manual note taking).

    Developing the access app was also an exercise in learning a new tool and a proof of concept for the app itself. I planned on eventually moving it to SQL Server Express. Looks like I might be making that change after phase 2. Then I get to learn how to install and administer SQL Server, with different instances and promoting changes between them. That sounds like a lot of fun!!

    Thanks again!
    Jim

    Tuesday, December 8, 2015 3:58 PM
  • In implementing, you have 2 choices, use the existing BE and replicate your BE changes in it, or use your new BE and have to refresh the data.

    ATGNWT,

    That certainly simmers down the available options. Unfortunately I don't have enough experience yet in Access to figure out how to accomplish the options.

    Thanks!
    Jim

    Tuesday, December 8, 2015 4:02 PM
  • Unfortunately I don't have enough experience yet in Access to figure out how to accomplish the options.

    Thanks!
    Jim

    Hi Jim,

    That's what we're here for. When you're ready to cross that bridge, we can guide you along the way. So, like I was saying earlier, I wouldn't worry too much about it. Everything will work out in the end.

    Just my 2 cents...

    Tuesday, December 8, 2015 4:13 PM
  • Is this a manual process similar to Albert's below? Or is this a function of Access that happens automatically. I would be interested in learning more about the process you use. I'm just beginning in the developer role so I may need some details on how to do it. Do you have any documentation or can you point me to the resources you used to develop the process.

    Hi Jim,

    The whole process is now completely automatic. It is not standard in Access, but home-made, and it is now standard in any application that I build.

    The steps are straightforward, but it needs some programming before you are there.

    - You need a form (or forms) to ask what needs to be changed, in terms of Table, Field, Action and Extra. The Action is create, rename or delete a Table; create, rename, delete a Field; create, rename, delete an Index; run a procedure;... The Extra depends on the Action, for instance for creating a Field you need information on the datatype.

    - You need a routine, that uses these four parameters to do the data-definition conversion. When you run this conversion routine in development mode, these 4 parameters are written in a Data table in FE, including a timestamp.

    Now you can continue to develop the FE based on the new data structure.

    In the BE you have in a table a record that contains the date of the last data definition update. In the start-up of the application the program checks whether there are records in the Data table. If no, it continues the start-up in the normal way. If yes, it processes all records from the Data table.
    If the timestamp is less then the last update, the record is deleted, else the conversion routine is run with the parameters in the record and after that the record is deleted. Finally you have an empty Data table, and the date of the last data definition update can be set to Now. Then the normal start-up is continued.
    In the development environment you then can empty the Data table, if you want.

    I can understand that you do not begin with such tools as a starting developer, but it can give you an insight that it is possible to completely automate this process.

    I hope this is all clear.

    Tuesday, December 8, 2015 4:59 PM