Version control

[Seth Schrock]

I'm currently running into a situation where I have a database that is currently running in production. I'm currently working on updating a part (lets say section A) of it. This update will take me several days to complete and now I have a high priority update that needs to happen. So now I have the production file, the development file that is the same version as the production file, and the new dev file with the changes that I'm working on now. And I need to make some changes quickly that will be put into production before I finish with the previous update. In the past I would just start a new version from the current dev file, make my changes, and then copy those changes into the dev file I was working on. Is there a way to modularize my program (code, forms, queries, reports, etc.) so that I can make changes to one part of the database without affecting another part? For example, if each main form along with all the popup forms and queries needed for it were in its own file, then I could make a change to two parts at the same time without having to copy changes over. Same idea as creating reusable modules so that you only have to write the code once.

How do you all handle these situations?

[zmbd]

Seth I think you are making things too difficult.

I start a new project then:
name_state_date _serial
MainLab_Alpha_2 0140101_0001
Now I use the serial to allow me to make a change and roll back
MainLab_Alpha_2 0140101_0002
If I like this then I basically abandon the 0001 file
Lets say I come back the next day, I make a copy of the 0002 file and rename
MainLab_Alpha_2 0140101_0003
say it takes me 11 days to work on this and have the boss sign off on it too...
MainLab_Alpha_2 0140101_0014
Now I take it to user review, make a copy append a seq
MainLab_Beta_20 140101_0014_01
need to tweek the next day, make a copy and change
MainLab_Beta_20 140101_0014_02
MainLab_Beta_20 140101_0014_03
etc..
If I like this then I abandon the 01 file
so every thing is happy and ready to go to production
MainLab_2014010 1_0014_03_01
backups
MainLab_2014010 1_0014_03_01_b0 1
MainLab_2014010 1_0014_03_01_b0 2
MainLab_2014010 1_0014_03_01_b0 3

Restored from backup b03
MainLab_2014010 1_0014_03_03

MainLab_2014010 1_0014_03_03_b0 1
MainLab_2014010 1_0014_03_03_b0 2
MainLab_2014010 1_0014_03_03_b0 3


Updates:
MainLab_Beta_20 140101_0014_03 >>> 04

released:
MainLab_2014010 1_0014_03_04_01

I do all of this from the OS side.
Once the file goes to production I keep:
Last Alpha and seq-1
MainLab_Alpha_2 0140101_0013
MainLab_Alpha_2 0140101_0014

Last Beta and seq-1
MainLab_Beta_20 140101_0014_03
MainLab_Beta_20 140101_0014_04

Last Pruduction and seq-1 and 3 rotaing backups
MainLab_2014010 1_0014_03_03
MainLab_2014010 1_0014_04_01
MainLab_2014010 1_0014_04_01_b1 0
MainLab_2014010 1_0014_04_01_b1 1
MainLab_2014010 1_0014_04_01_b1 2

A back end would be:
MainLab_Data_20 140101_0014_03_ 01
MainLab_Data_20 140101_0014_03_ 01_b10
MainLab_Data_20 140101_0014_03_ 01_b11
MainLab_Data_20 140101_0014_03_ 01_b12

restored wold be
MainLab_Data_20 140101_0014_03_ 12

In the network:
Current version launcher: \\networkpath\Z LIMS\MainLab\
Current front end: \\networkpath\Z LIMS\f\MainLab\
Current front end bu: \\networkpath\Z LIMS\b\f\MainLa b\
Current Back end: \\networkpath\Z LIMS\MainLab\da ta\
Current Back end bu: \\networkpath\Z LIMS\b\MainLab\ data\
Development front end: \\networkpath\Z LIMS\dlv\MainLa b\
Development Back end: \\networkpath\Z LIMS\dlv\MainLa b\data\
Development recovery: \\networkpath\Z LIMS\dlv\MainLa b\r\


Now this Current version launcher \networkpath\ZL IMS\MainLab\ has some vba that checks for a copy of the file on the local \public user\desktop. If the same as the \\networkpath\Z LIMS\f\MainLab\ file then it starts another instance of Access and the local front end, if not then it copies the front end from \\networkpath\Z LIMS\f\MainLab\ to the \public user\desktop and then starts another instance of Access and the local front end.
It also checks the sequence at the end of the local front end....
Say I'm at MainLab_Data_20 140101_0014_04_ 12
However the local copy is MainLab_2014010 1_0014_04_01
The code that handles the versions will not replace the Seq01 with Seq12... instead in \\networkpath\Z LIMS\dlv\MainLa b\r\ a new folder named with the current date and time is created and the Seq01 file copied to it. Then the user is informed that there appears to be a front end of the same version restored from backup on the network does the user wish to overwrite the local copy?
The front ends have code to check the network folder, check for versions, and sequence just as the launcher does and handles things along the same way.
Yes, that has taken me years to develop and it doesn't always go without a hitch.

[jimatqsi]

"Is there a way to modularize my program (code, forms, queries, reports, etc.) so that I can make changes to one part of the database without affecting another part?"

Sure, but you're not likely to make such a switch overnight. It's possible but there are lots of issues to consider. If you talk to a dozen programmers you'll probably get a dozen responses on how to do this - unless they all work for the same MegaCorp in which case they think there's only one way.

But first, if you are considering actively modifying 3 different versions of the same program, hoping to piece them together later, you're asking for certain trouble. You might just as well start writing your explanation for management about what went wrong.

You could modularize by using multiple front-ends. I have one front-end in the warehouse and a different one in the call center. (And there is one minor form that I maintain in both front-ends). I have another front-end that does all the autonomous stuff like directing pick tickets to the right warehouse printer, error-checking and some other things that are best left in the background.

I think the way you are doing your updates now is hard and fragile. Copying objects from the development copy into the live copy is prone to failure when you forget that some query related to that form you copied also changed, or some table def has to change as well, or a host of other things. At least that was my experience when I tried to work that way.

Jim

[Seth Schrock]

@Z So how do you handle it if you have the production file being used: MainLab_2014010 1_0014_03_01. You start working on an update for section A, so you have your dev file MainLab_beta_20 140101_0014_04 and you are working on it for a couple of weeks. Now your boss says that Section B needs updated NOW. It will take a couple more weeks to finish the update for section A, so you have to start back over with the same version as your production file. You get the update for section B done, but now your file that you are working on to update section A doesn't have the section B update. So if you publish your section A update, then you will loose the section B update. So you have to then copy the changes from your section B update into your file for the section A update so that you now have a file with the latest versions for both sections. This is what I'm trying to avoid and I don't see how your system solves that. Maybe I missed something.

@Jim I don't copy forms, queries, code, etc. from my dev file to my production file. Once I have my dev file working, I make a copy of the whole file and place it in the install folder for my vbscripts to be able to copy to each user. Where I am copying the individual objects in in a situation like I described above where I have to merge two update dev files into one so that I have all the latest versions of each section all in one Access file. I would love to have multiple front ends if I could link them into one program kind of like linking tables, but I don't know how to or if it is possible to do so. Like a website, where each page is a separate file. I could update one page without messing up the rest of the website.

[zmbd]

Seth Schrock
@Z So how do you handle it if you have the production file being used: MainLab_2014010 1_0014_03_01. You start working on an update for section A, so you have your dev file MainLab_beta_20 140101_0014_04 and you are working on it for a couple of weeks. Now your boss says that Section B needs updated NOW.

So for example you have two forms in and altering form_a when the boss wants form_b altered too and this alteration is an emergency.

So the current production file is:
MainLab_2014010 1_0014_03_01
(most likely a MDE or ACCDE for me as I rarely push them out in any other format)

This tells me that the last beta was:
MainLab_beta_20 140101_0014_03

Because this isn't something minor such as a spelling error
MainLab_beta_20 140101_0014_03

roles back to alpha and advances the sequence
MainLab_alpha_2 0140101_0015

(If All am am doing is moving a button location, or correcting a spelling error etc... then the Beta file sequence would roll forward. Anything that effects the underlying code or SQL of a form or that adds (i.e. new report) roles back to alpha state --In the past, I've even pulled a beta version back to alpha because there were too many changes required to the underlying database. I've not had to do that for quite sometime now as I usually have a very good table structure and plan of action laid down before I ever get to the forms)

In alpha_15
I now make a copy of Form_A and all code related there-in and rename to Form_A_P2014010 1_0014_03_01
(I will also make a copy and rename queries that have to the altered for this work too.)
This is a fail safe in-case I really goof
AND
for your situation.



Now I start my work in Form_A

Say I'm on day four plus two rollbacks so I'm now working in
MainLab_alpha_2 0140101_0021

and the boss asks for that emergency change in Form_B that must take priority over the current Form_A work.
We advance to MainLab_alpha_2 0140101_0022
Make a Copy of Form_B rename to From_B_P2014010 1_0014_03_01
Because we do not know if the emergency changes in b will break a we need to rename the Form_A to Form_A_Alpha_00 21
Rename Form_A_P2014010 1_0014_03_01 back to the "Form_A" status.
(remember if you altered any queries or reports, you will need to roll them to the alpha and restore the original)

Now work in the Form_B in MainLab_alpha_2 0140101_0022
Say this takes two days: MainLab_alpha_2 0140101_0024 and form_b changes are made and work with the current form_a

When your ready for the boss to validate the changes to form_b
MainLab_Beta_20 140101_0024_01
two minor changes to form_b
MainLab_Beta_20 140101_0024_03

All is good so roll to:
MainLab_Beta_20 140101_0024_04 delete/clean-up form_b_P2014010 1_0014_03 clean up the other objects backed up for form_b work and roll to:

and we roll forward to the MDE or ACCBE
MainLab_2014010 1_0024_04_01

Ahh - now back to our work on form_a
MainLab_Beta_20 140101_0024_04
rolls to
MainLab_Alpha_2 0140101_0025

The current Form_A is renamed back to Form_A_P2014010 1_0014_03 and Form_A_Alpha_00 21 is renamed to Form_A
Now, we have to take care, take a few minutes and look a any objects that were altered for our form_b work vs. the Alpha_21 mods... we need to integrate these changes into Form_A if needed and proceed.

YES, the current production file has the Form_A_Alpha_00 21, and alphas for any other objects, in it; however, there's really no issue with this as when the work is done those artifacts will disappear from the production file.

It seems in my prior post I forgot to mention the roll back from beta to alpha - this has to do with my belief in backups and redundancy. I never have a single production file always a backup, never a single development file... etc...

[NeoPa]

When I need to do some urgent work in the middle of a more major update I go back to the released version and make the changes there. While making the changes I keep a note of the objects that get updated, although determining which have changed is mostly possible after the fact by checking the dates of the objects in Detail View.

Once the changed version has been released I incorporate the updates (using Import or Export depending on preference) into the version where I was recently working on the major update and test that nothing about the new update has interfered with anything that it shouldn't have. As both jobs were both worked on quite recently I find that fallout issues are very rare and, when found, they can be handled easily as I understand what both sets of changes were and what they were for.

[Seth Schrock]

So it sounds like I'm just stuck copying the changes from one update to the other. Bummer. Thanks for your help and I'll have to look at your versioning a bit closer to get it totally figured out Z.