Split Database Concept (1 Viewer)

[theDBguy]

OK I guess this was the key point I was missing. Time to research ACCDB vs ACCDE. I think I know the difference. which usually means I just think I do and really don't.

And your point about "overwriting" vs changing the BE directly is noted. I work in a very small office where max users are 5 at any one time. And It would be rare for more than 2 users at a time. I have a tendency to overlook some things as a result.

So in summary, I split the database and put the BE on the server and a copy of the ACCDE goes to each user. I then take a copy of the BE and keep it on my PC and use ACCDB for future changes etc. to the FE. When my change is done, I make a new ACCDE and distribute to the users. Sound right?

Some developers don't care if the users make any design changes to the FE. Some users are savvy enough to help themselves and make their job easier by tweaking the database they're using. However, they need to accept the fact that once you distribute a new version of your FE, all their "custom changes" will disappear. They will have to do all their work all over again. Just a thought...

 

[The_Doc_Man]

When I was a Navy contractor, we used this method for developing a "live/busy" application. "Live" in the sense that users were likely to be on/in it at various times during the work day; "busy" in the sense that the odds favor the DB being in use at any convenient time that YOU would have wanted to work on it.

We kept four folders for our app. The DEV folder held the master development copy as an .ACCDB and was NEVER EVER visible to the world. We had a BE file to go with the FE file. The TEST folder held the latest copy of the FE file and had a testing DB. If something went wrong in testing, we just round-filed the FE and went back to work on the DEV copy.

Eventually we would get a DEV copy that passed tests well enough to be the next "live" version. So we finished testing and moved the successful file to STAGING folder. That folder had no database BE file in it unless we were diddling the BE structure. (More about that in a minute.) When the FE was in STAGING, we "hardened" it to make the works not visible to the users. We had a control-panel form that NEVER closed, and our users picked what they wanted to do from the controls on the control panel. They NEVER saw the navigation pane or ribbon. (There are tons of articles on the forum on how to do that.) Whatever we were going to do the to FE to finally prepare it for users, we did in STAGING. That file would be whatever we were going to make public. So we copied it to the PROD folder where it would have replaced the previous FE file. So we copied the previous FE to the BACKUP folder and added a date to its name.

When we HAD to diddle the BE file, we diddled the BE copy first and took detailed notes. On the day of the BE update, we renamed the PROD folder copy of the BE file to the STAGING folder. Because the link to the DB depends not only on name but on path, that meant that the DB was out of service. So we consulted our notes and updated the BE as best we could, then renamed it back to PROD when we were finished.

Updating the FE file (by itself) was almost trivial, mechanically speaking, because it was a copy & rename for the old file to the backup folder and a copy of the staging file to the production file. Updating the BE file was trickier because we needed to schedule a "down time" for the DB. In this forum you can search for "kick users out" and see various methods we had of getting our users to log out so we could do required updates. If we were updating the BE, by implication we HAD to be updating the FE - at least with some new tabledef stuff - so we always coordinated FE updates to the next available BE update date.

Tedious? You bet your bottom dollar it was a pain in the toches - but it kept everything clean.

 

[Isaac]

If I have to add a new Table or a new Form, I have to do this in the Access Database which is located in "M:\Master Database\All Programs.Accdb". After Adding or Editing the Tables and Forms, I need to again Split the Database and copy the Updated Access Database "M:\Master Database\All Programs.Accdb" to my 20 User's Computer.

A minor modification "I need to again Split the Database..."

No. You just continue developing Forms, Reports, Queries and Code in the front end. The splitting is a one-time thing.

 

[Pat Hartman]

Making BE changes is a delicate process. You cannot be changing the structure of the BE when the users are doing their thing. You have to have the BE to yourself. When I need to make changes to the BE, I use different techniques depending on what needs to be changed. Back up early. Back up often during the change process. You always need a fall back position.

If I need to add some fields or tables, I stay late some evening or remote in from home. If there is no lock file, I know there are no active users so I rename the BE just to make sure that no one can sneak in on me. I back up the database and compact it. Then I add the new columns/tables, save and close. The last step is to name the changed version back to its original name. This lets me modify the master copy of the FE and make changes to use the new columns and tables. The new columns and tables in the BE do not interfere with any existing forms/queries/code.

If I am renaming something or splitting a table because of a design change or perhaps retiring a table, I make a change script. I create DDL (data definition queries) or DAO code that transforms the object to whatever I need it to be. Once the change script is complete, I put it away for later after creating a test BE for my changed FE to use. Once I am happy with the changes, I need to run the change script against the production version of the BE. Again, it is best to do this in the evening or on a weekend when you can be sure no users are going to interfere. Start again with compact and backup to a zip file. Rename for safety. Run the change script. Create a backup of the changed schema. Rename back to the original name, Relink the test FE and test again with the production FE - carefully because you'll need to back out any changes. If you make changes you can't back out, you need to delete the version you updated and unzip the second zip so you now have the "clean" modified BE. Now you can move both the FE and BE back into production. Make sure the names are cleaned up if you changed them and both FE and BE are back in place.

 

[gemma-the-husky]

@Pat Hartman is spot with her advice.

Note that you can change an in-use back end, but it depends which table you are changing.

Say you want to add a new field to a little used table - maybe add a notes column to a status table.

You can do that if the table is not in use, without a problem. If the table is in use, you will get a warning that you cant; make changes, and you can leave it until a more suitable time.

Anything complex needs careful planning.