How to Keep track of Documentation for BE / FE Revisions

[Curious]

Hello everyone,

I have been wondering about how I'm going to keep track of revisions for one of my databases.

Currently, I have the database split into a Back End (BE) and Front End (FE). In the work environment, the BE sits on the server, whilst all other 4 machines get a local copy of the FE. All table data in stored in the BE and linked to the FE.

I have recently got my act together and started documenting all the revisions I make to the BE and FE of the database. To do so, I created two tables with the following fields:

RevID (PK Auto increment),
RevDate
RevNumber
RevisionNotes
TimeDateStamp


I called them tblRevisions_FE, tblRevisions_BE. I placed both tables in the BE.

Whenever I make changes to BE / FE, I record it in the appropriate table.

However, because I often work on the FE of the database off site, I don't have access to the BE table (tblRevisions_FE) to record the new changes for the FE.

One alternative I thought of was just recording the revisions in a text document, and asking the client to manually record these in the tblRevisions_FE (via a FE form).

I did have tblRevisions_FE located in the FE, but when each db user got a copy of the FE, there would also be 4 copies of the table floating around.

Does anyone have any suggestions about how to manage documentation of FE revisions in this situation?

 

[boblarson]

I would just leave the frontend table in the frontend and, since you would be controlling the frontend master, it really doesn't matter if they have that table as your master would keep the changes and you do just replace the frontend file completely, don't you?

 

[Curious]

Yes, I think that would be fine, if the staff were not also making changes.

One of the staff make changes to the FE to make extra reports and I have asked him to document these. Which is why I first took the table out of the FE.

I was concerned that if he made a change, and recorded it in tblRevisions_FE, which copy of the 4 it would change. And what if he added to the tblRevisions_FE on other local machine the next time he did upgrades. By putting it in the BE, the data in the table would be centralized.

Is there another possibility?

 

[boblarson]

Well, if that is the case then yes, the backend would probably be the best. But, if they make changes to the front-end, how do you maintain source control over 4 different front-ends?

I think if they wanted extra reports for themselves, or something, I would have them link to the database and do their own stuff outside of the front-end. But, that's just my preference.

 

[Curious]

I always keep a copy of my last FE update before sending an update through. They will put a copy of the FE on each local machine.

Once it's in the hands of the company, they may make changes to the FE according to their needs. This updated FE would then be copied again to local machines across the network.

I guess I'm really being overcautious. The on-site techie, who makes the new reports / queries, would have to be aware of which FE version he is changing, in order to distribute the changes to all computers.

I think I'll will move the FE Revisions table back to the FE. The on site techie would soon realize if he's not working on the latest copy of the FE by date, or by reviewing the latest revision notes in the table.

When I do an update, I'll ask for the current FE, upgrade it and send it back to overwrite the old FE.

Thanks for helping me think this one through boblarson.

 

[MicroE]

You could keep your changes in the FE and the users changes in the BE. A union query cam be used to pull the results.

I personally put my change log in the FE. This approach is great when you are looking through old FE files. You can always tell what version you have because the change history table is in the FE.

Typically, I don’t allow my users to make FE/BE changes. If they need to create their own queries and reports - I create a separate database for ad-hoc reporting.

 

[Curious]

I like that idea:

Typically, I don’t allow my users to make FE/BE changes. If they need to create their own queries and reports - I create a separate database for ad-hoc reporting.

So do you give the company a locked MDE version whenever you do an update to the FE or BE?

And then simply create a new database that will link to all the tables in the BE which will allow the company to create their own Ad-hoc reports?

It sounds wonderful for a few reasons:

1. I maintain control of the database system I created and can protect my work.
2. The company gets the benefits of being able to create additional reports without having to incur additional costs everytime they want a new report.
3. I know what changes are mine and what changes are the companies.

I love it!! I think that is exactly what I'm going to do.

Thanks a lot MicroE!

 

[MicroE]

Yes – that is exactly what I do.

If I want to make sure they can’t change the data – I would create a routine that runs every morning to copy the BE to another location. Then point the ad-hoc FE to the copy – this way users can’t unintentionally mess up the data. Link

 

[Curious]

Hi MicroE,

Your article link was very informative. I'll try a few of those other options. The batch file sounds like a great idea.

I've been thinking how the company would manage their new reports if I locked up my FE database.

E.g. Let's say I create the FE MDE version, with a switchboard to all forms and reports needed. Then the company creates an extra 10 reports that they will use to track monthly figures in another FE db linking to the BE.

Is it possible to create a switchboard that would allow users to access forms/reports from both FE versions?

Of course, I can always integrate them into my MDE FE version at a later stage, but suppose they wanted to manage this internally, would it be possible?

How do you protect your work if you can just import the queries, forms, and reports from your locked MDE FE version, into a new database and play with the source code?

(This thread started off about documenting revisions, but I think my main concern was security)

 

[MicroE]

I generally cerate a command button on the main menu that opens the reporting database.

Report users create can be intergraded at a later point in time by the developer (you).

Allowing users to create reports in the application will only lead to confusion and frustration. They may overwrite your changes - you could overwrite their reports. I just usually explain these concerns to the customer.

The objects in the MDE are locked down – they can’t be imported into a new db (only tables and queries can).