I've been researching this question over the last few days as I prepare to deliver the first of 3 phases with my first system using a split database. I would like your advice as I haven't found enough info to make a full decision yet.

At the moment I'm working in dev on an unsplit database. When I split it in live I'll take a copy of both parts but what do I do with them for phase 2?

I'm thinking that I'll now make them my dev version after relinking the tables (as I've effectively moved the back end) which would then mean that I no longer work with the unsplit database. Is that the right approach?

When it comes to putting phase 2 live I don't think I have any choice other than manually applying table updates to the live back end (once backed up). For the live front end, do I just replace it with my dev front end and then relink the tables or do I export the changes into the live front end? I guess I could do either depending on the number of objects that are changing/new. Is that right? I'll then take copies and make them my dev versions for phase 3.

Finally in dev I have form and report templates and test forms, reports and queries which are not needed in live so do I remove them for each deployment and then add them all back in to the new dev front end or just put them live? Normally I would take them out but there are a lot of them and I don't know of any quick way to add them back in so what do you do?

Primarily my questions are asked from a risk point of view - what steps best reduce the risk of messing things up in live.

CodePudding user response：

For reducing risk: you should have your development version, a test version for live and the live version. You are developing on develop Customer tests the changes on test (with test data) after that you move to live

For the move from develop to test I create an update/migration script. In this script are all alterations included that needs to be done on the back-end. I use the script to create the test version and with this I can check if it is working properly. In case there are database changes that I can't reflect in my script (either insufficient skill or restrictions from db) I add them to my checklist.

I am using version control to see changes during development and to import modules, queries etc. to the new version.

Updating the front end is done via Import of the latest version (without not needed forms / reports).

CodePudding user response：

At the moment I'm working in dev on an unsplit database. W

Don't, a VERY bad idea. How this works?

Well, for sure at the start, you are building LOTS of new tables, changing relationships, and building tables at a high rate of development.

so, at this point in time, you can develop un-split.

You will then find after some time the rate (and need) to create new tables, and change the so called "database schema" calms down to a dull roar.

At this point your still developing away - not yet deployed.

So, somewhere around this point? You want to split. You REALLY want to do this.

There is a boatload of reasons for this, but several are:

while a split vs non split is "very similar", they are not the same!
Thus, you can't and don't want to develop code that NOT really tested
as to HOW the code will run in the real world

So, many issues can change or crop up during development that is DIFFERENT when run split. So some commands (such as seek()) don't work, and a few other issues can crop up. You don't want to develop for a whole week, then split and now find 20 hard to fix bugs in your code. So, by developing as split as soon as possible, then ANY and ALL issues that come up will be seen as you develop along, and thus can fix, see, and deal with such issues at THAT POINT in time. Much worse is to write a bunch of code, get ready to deploy, and then find new bugs.

Next up: Having a split system is great, since say a customer might let you remote into their system. You can pull down a copy of their data, re-link your tables to point from your "test data" to real live production data.

Or, say your developing on site. You might want to test some dangerous delete code, or code that modifies the data. So, you can't risk working on production data, so now you re-link and point to your test back end. So, this setup allows you to test code, but MORE important test on a copy of the database with great ease.

And it also allows you to develop off site. You can take the latest front end for their system, maybe get their latest data file, maybe not. but you can now with ease simple change the database that your applcation runs with.

The other big issue? Say your working on site, and have a test database of theirs on the network folder. You write some code, test for a new report. You find it runs SLOW AS A TURTLE. You check your code, maybe add a few indexes, and boom! - your report now runs great.

If you test un-split, then a boatload of performance issues can crop up, but NOT SEEN during the development cycle. Once again, you don't want to develop for weeks or whatever, split, and NOW find a whole bunch of forms and code runs REALLY slow.

So, the goal, the idea here?

You want to get split as SOON as possible?

How soon?

Well, this is one of those things that only you can know!!!

As I stated, at start of development, sure, start out un-split.

Once the table designs are quite solid, then you can split. You then ALWAYS develop as split (and the above list of reasons why is the VERY short list - there are many more reasons).

Now, the problem of course with split? Say you want to add a new column to a table?

Well, it is MORE work, since now ANY AND ALL changes to the data schema are done in the back end. So, you have to close down the front end (FE), open the back end, and now use the table designer to add that one column. (or maybe change or setup a new relationship between some tables. Or maybe add a new table).

This is a "bit more" work, since now you close down the BE, and open the FE, and now you MUST re-link tables. And if you added new tables to the BE, then you have to add that/those new table links.

Because this "dance" is extra work, that is why you wait as long as possible to split. As I stated, you "just know" when that time has arrived to split. (when table and schema tables changes settles down to a low rate of change). Since the rate of change is now low in regards to table changes, then it not much work nor pain to have to do the above every time you want to change the table structures.

In fact, think of any program you buy? It has a applcation part, and then a data file part. In effect, once you split, you have the same two parts, and in fact in some applications I written, they are allowed to use "different" back ends - not unlike any other applcation in which you launch the applcation, and THEN choose the datafile to work with.

So, what about developing off site? Well, that can be REALLY difficult, since you have your own copy of the FE and the BE.

If you HAVE TO make changes to the BE? I open up a work document. And if I add a new column to say tblCusotmers?

Then I enter this:

Add new column TaxRate, Currency, to table tblCustomers

So, you build up a "log" of changes. Now, when you travel to the customer site, and want to roll out and deploy the new FE? Well, you have to FIRST open up their BE, and make the above changes to their production BE database.

Now in some cases, where is was not possible for me to be on site? (in fact, I had a automatic update system to automatic roll out a new version of my software - and it would automatic down load from the internet. In this case? I had to write code in the FE on startup that would use VBA code to MAKE the changes to the data tables. This can be REALLY hard to do, but is possible. I just recommend the plane jane word document, and you keep track of your changes.

So, the above is quite much how this development process works.

Since you will have to re-link the tables? Then near everyone has googled for a VBA table re-link routines. You want that, since having such code is MUCH easier then say using the linked table manager each time. And we even often have a table in the FE that saves the BE server location, and on startup will check, and if the location of the files don't match, we launch the re-link code.

that way, you can deploy the applcation to different sites, and have it automatic re-link. Another way is to have a simple text file in the same location as the FE on each computer, and on startup read the text file with the BE location - and re-link if required.

So, the typical process to role out a new FE (which is placed on each work station - do NOT break this rule!!!).

So, I point/relink my front end to the production BE. I then compile down to a accDE, and then deploy that new compiled FE to all the work stations. In fact, I have some code in VBA at start up that compares a version number, and if the version number is lower, then the VBA code will copy down the next FE sitting in a folder.

This might not be a big deal if you have 2-4 users. But, if you have two sites, and each has 35 users, then you want to figure out a automated approach.

However, do not prolong jumping over to the split development cycle, as you really for all practical purposes MUST develop in a split environment. So, for the first part, you can develop un-split. But, once you split - that's it, and from that point on-wards, you are to develop as split. There are boatloads of benefits, but it also really quite much standard approach from a developer point of view.

So, you have to master the linked table manager rite quick, and then VERY much consider adding some re-link code, since you want with great ease to point to a different back end - including at deployment time.

so, as a general rule, you should think of your FE like a .exe program, for a new version roll out, yes, you copy (over write) their existing FE's on each work station. And as noted, in most cases, it should be a compiled accDE, and not a un-compiled accDB for the FE.